If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
finding data in the same sheet
Hi, I wonder if any one can help I have a work sheet where in A4 down to A40,
I type in a list of names, B4 to Y4 down to B40 and Y40 contains data with an average in Z4to Z40. In AP4 down to AP 80 I have another list of names which I need to keep in the same order. What I would like to do is: - say AP4 Has the name John Doe in it, I would like to look in A4 to A40 find John Doe and copy the figure in Z4 to Z40 so if John Doe is in A5 then copy the data from Z5 and paste it into AQ4. I would then like to make 28 copies of the sheet in the workbook. Thank you in advance to any one who has a go at this. Barry. |
#2
|
|||
|
|||
finding data in the same sheet
This appears to be standard lookup type approach. The two most effective are
INDEX/MATCH & VLOOKUP. In AQ4, put this formula and see if it gets what you want: =INDEX($Z$4:$Z$40,MATCH($AP4,$A$40:$A$40,0)) ....and copy that down. It should match each name in the AP column and grab the data in Z column that matches that name in the A column. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Barry" wrote: Hi, I wonder if any one can help I have a work sheet where in A4 down to A40, I type in a list of names, B4 to Y4 down to B40 and Y40 contains data with an average in Z4to Z40. In AP4 down to AP 80 I have another list of names which I need to keep in the same order. What I would like to do is: - say AP4 Has the name John Doe in it, I would like to look in A4 to A40 find John Doe and copy the figure in Z4 to Z40 so if John Doe is in A5 then copy the data from Z5 and paste it into AQ4. I would then like to make 28 copies of the sheet in the workbook. Thank you in advance to any one who has a go at this. Barry. |
#3
|
|||
|
|||
finding data in the same sheet
Hi,
Try =VLOOKUP(AP4,A$4:Z$40,25,) or =LOOKUP(AP4,A$4:A$40,Z$4:Z$40) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Barry" wrote: Hi, I wonder if any one can help I have a work sheet where in A4 down to A40, I type in a list of names, B4 to Y4 down to B40 and Y40 contains data with an average in Z4to Z40. In AP4 down to AP 80 I have another list of names which I need to keep in the same order. What I would like to do is: - say AP4 Has the name John Doe in it, I would like to look in A4 to A40 find John Doe and copy the figure in Z4 to Z40 so if John Doe is in A5 then copy the data from Z5 and paste it into AQ4. I would then like to make 28 copies of the sheet in the workbook. Thank you in advance to any one who has a go at this. Barry. |
#4
|
|||
|
|||
finding data in the same sheet
wow cool thanks JB
"JBeaucaire" wrote: This appears to be standard lookup type approach. The two most effective are INDEX/MATCH & VLOOKUP. In AQ4, put this formula and see if it gets what you want: =INDEX($Z$4:$Z$40,MATCH($AP4,$A$40:$A$40,0)) ...and copy that down. It should match each name in the AP column and grab the data in Z column that matches that name in the A column. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Barry" wrote: Hi, I wonder if any one can help I have a work sheet where in A4 down to A40, I type in a list of names, B4 to Y4 down to B40 and Y40 contains data with an average in Z4to Z40. In AP4 down to AP 80 I have another list of names which I need to keep in the same order. What I would like to do is: - say AP4 Has the name John Doe in it, I would like to look in A4 to A40 find John Doe and copy the figure in Z4 to Z40 so if John Doe is in A5 then copy the data from Z5 and paste it into AQ4. I would then like to make 28 copies of the sheet in the workbook. Thank you in advance to any one who has a go at this. Barry. |
#5
|
|||
|
|||
finding data in the same sheet
Thank's Shane, I will give that a go.
"Shane Devenshire" wrote: Hi, Try =VLOOKUP(AP4,A$4:Z$40,25,) or =LOOKUP(AP4,A$4:A$40,Z$4:Z$40) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Barry" wrote: Hi, I wonder if any one can help I have a work sheet where in A4 down to A40, I type in a list of names, B4 to Y4 down to B40 and Y40 contains data with an average in Z4to Z40. In AP4 down to AP 80 I have another list of names which I need to keep in the same order. What I would like to do is: - say AP4 Has the name John Doe in it, I would like to look in A4 to A40 find John Doe and copy the figure in Z4 to Z40 so if John Doe is in A5 then copy the data from Z5 and paste it into AQ4. I would then like to make 28 copies of the sheet in the workbook. Thank you in advance to any one who has a go at this. Barry. |
Thread Tools | |
Display Modes | |
|
|