Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of lookup formula required
Let's say for eg I have 5 worksheets labelled A,B,C,D,E.
Worksheet A has in column A the alpha-numerical values A1-A250 going down the page, with one value per row. Worksheet B has values A251-A500 again in column A going down the page, the same with sheet C & D up to the value A1000. I want to be able to type a value in cell A1 of worksheet E, and then rows 2-10 of the same column to return the succeeding values as per worksheets A,B,C,D. For eg if I were to type A376 in cell A1 of Sheet E, I would want cell A2 to pull through A377 from sheet C, A3 to pull through A378 from sheet C etc etc. Can this be done? Have I made any sense? Please help. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of lookup formula required
Put this in Sheet E cell A2 and copy down..........
=INDIRECT(LOOKUP($A$1,{"a1","a250","a500","a750"}, {"A!","B!","C!","D!"})&LEFT($A$1,1)&MID($A$1,2,99) +ROW(A2)-2) Note that it's all on one line, watch out for word-wrap. Vaya con Dios, Chuck, CABGx3 "Syndrome" wrote: Let's say for eg I have 5 worksheets labelled A,B,C,D,E. Worksheet A has in column A the alpha-numerical values A1-A250 going down the page, with one value per row. Worksheet B has values A251-A500 again in column A going down the page, the same with sheet C & D up to the value A1000. I want to be able to type a value in cell A1 of worksheet E, and then rows 2-10 of the same column to return the succeeding values as per worksheets A,B,C,D. For eg if I were to type A376 in cell A1 of Sheet E, I would want cell A2 to pull through A377 from sheet C, A3 to pull through A378 from sheet C etc etc. Can this be done? Have I made any sense? Please help. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of lookup formula required
I experimented with 25 (not 250) entries on a sheet.
This seems to work =IF(ROW()<(25*INT(RIGHT($A$2,LEN($A$2)-1)/25)+25)-(RIGHT($A$2,LEN($A$2)-1))+1,INDIRECT(CHAR(39)&CHOOSE(INT(RIGHT($A$2,LEN( $A$2)-1)/25)+1,"A","B","C","D")&CHAR(39)&"!A"&MOD(RIGHT($A$ 2,LEN($A$2)-1),25)+ROW()-2),"") best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Syndrome" wrote in message ... Let's say for eg I have 5 worksheets labelled A,B,C,D,E. Worksheet A has in column A the alpha-numerical values A1-A250 going down the page, with one value per row. Worksheet B has values A251-A500 again in column A going down the page, the same with sheet C & D up to the value A1000. I want to be able to type a value in cell A1 of worksheet E, and then rows 2-10 of the same column to return the succeeding values as per worksheets A,B,C,D. For eg if I were to type A376 in cell A1 of Sheet E, I would want cell A2 to pull through A377 from sheet C, A3 to pull through A378 from sheet C etc etc. Can this be done? Have I made any sense? Please help. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of lookup formula required
I blush when comparing this to my solution!
But I assumed his data was not really A+number -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "CLR" wrote in message ... Put this in Sheet E cell A2 and copy down.......... =INDIRECT(LOOKUP($A$1,{"a1","a250","a500","a750"}, {"A!","B!","C!","D!"})&LEFT($A$1,1)&MID($A$1,2,99) +ROW(A2)-2) Note that it's all on one line, watch out for word-wrap. Vaya con Dios, Chuck, CABGx3 "Syndrome" wrote: Let's say for eg I have 5 worksheets labelled A,B,C,D,E. Worksheet A has in column A the alpha-numerical values A1-A250 going down the page, with one value per row. Worksheet B has values A251-A500 again in column A going down the page, the same with sheet C & D up to the value A1000. I want to be able to type a value in cell A1 of worksheet E, and then rows 2-10 of the same column to return the succeeding values as per worksheets A,B,C,D. For eg if I were to type A376 in cell A1 of Sheet E, I would want cell A2 to pull through A377 from sheet C, A3 to pull through A378 from sheet C etc etc. Can this be done? Have I made any sense? Please help. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of lookup formula required
CRL
I tried to understand the formula and created worksheets, but it gives Zero in the results. Aqib CLR wrote: Put this in Sheet E cell A2 and copy down.......... =INDIRECT(LOOKUP($A$1,{"a1","a250","a500","a750"}, {"A!","B!","C!","D!"})&LEFT($A$1,1)&MID($A$1,2,99) +ROW(A2)-2) Note that it's all on one line, watch out for word-wrap. Vaya con Dios, Chuck, CABGx3 "Syndrome" wrote: Let's say for eg I have 5 worksheets labelled A,B,C,D,E. Worksheet A has in column A the alpha-numerical values A1-A250 going down the page, with one value per row. Worksheet B has values A251-A500 again in column A going down the page, the same with sheet C & D up to the value A1000. I want to be able to type a value in cell A1 of worksheet E, and then rows 2-10 of the same column to return the succeeding values as per worksheets A,B,C,D. For eg if I were to type A376 in cell A1 of Sheet E, I would want cell A2 to pull through A377 from sheet C, A3 to pull through A378 from sheet C etc etc. Can this be done? Have I made any sense? Please help. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of lookup formula required
Thanks Bernard, every once in a while I get lucky <G.....ordinarily I'm the
one standing in awe of your solutions........ Vaya con Dios, Chuck, CABGx3 "Bernard Liengme" wrote: I blush when comparing this to my solution! But I assumed his data was not really A+number -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "CLR" wrote in message ... Put this in Sheet E cell A2 and copy down.......... =INDIRECT(LOOKUP($A$1,{"a1","a250","a500","a750"}, {"A!","B!","C!","D!"})&LEFT($A$1,1)&MID($A$1,2,99) +ROW(A2)-2) Note that it's all on one line, watch out for word-wrap. Vaya con Dios, Chuck, CABGx3 "Syndrome" wrote: Let's say for eg I have 5 worksheets labelled A,B,C,D,E. Worksheet A has in column A the alpha-numerical values A1-A250 going down the page, with one value per row. Worksheet B has values A251-A500 again in column A going down the page, the same with sheet C & D up to the value A1000. I want to be able to type a value in cell A1 of worksheet E, and then rows 2-10 of the same column to return the succeeding values as per worksheets A,B,C,D. For eg if I were to type A376 in cell A1 of Sheet E, I would want cell A2 to pull through A377 from sheet C, A3 to pull through A378 from sheet C etc etc. Can this be done? Have I made any sense? Please help. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of lookup formula required
The formula is to be placed in cell A2 of Sheet E, and assumes the following
data is in place: 1-some value between A1 and A1000 is entered in Sheet E, cell A1 2-Sheet A, cells A1:A250 are populated with values 3-Sheet B, cells A251:A500 are populated with values. 4-Sheet C, cells A501:A750 are populated with values. 5-Sheet D, cells A751:A1000 are populated with values. All, IAW my understanding of the OP's specs. Then, the formula looks up the value in A1 and compares it to the prescribed list within the formula to return a "SheetName" with an exclaimation mark to start a concatenated string of the final formula. The formula goes on to concatenate the leftmost character in cell A1, (the A) and the numerical value that follows the A, and qualifies it with the ROW number and increments it for copying down the column.......the whole concatenated string being converted by the INDEX term to a formula for which to return the desired value....ie, with A257 in Sheet E, cell A1, the equivelent formula in A2 would be =B!A257, and in A3 would be =B!A258, etc etc....my long formula just does all the lookup and conversion for you. I don't know why you are getting the zero result unless one of the above conditions is not in place.......double check, and if you still get the same result, post back. hth Vaya con dios, Chuck, CABGx3 "Aqib Rizvi" wrote: CRL I tried to understand the formula and created worksheets, but it gives Zero in the results. Aqib CLR wrote: Put this in Sheet E cell A2 and copy down.......... =INDIRECT(LOOKUP($A$1,{"a1","a250","a500","a750"}, {"A!","B!","C!","D!"})&LEFT($A$1,1)&MID($A$1,2,99) +ROW(A2)-2) Note that it's all on one line, watch out for word-wrap. Vaya con Dios, Chuck, CABGx3 "Syndrome" wrote: Let's say for eg I have 5 worksheets labelled A,B,C,D,E. Worksheet A has in column A the alpha-numerical values A1-A250 going down the page, with one value per row. Worksheet B has values A251-A500 again in column A going down the page, the same with sheet C & D up to the value A1000. I want to be able to type a value in cell A1 of worksheet E, and then rows 2-10 of the same column to return the succeeding values as per worksheets A,B,C,D. For eg if I were to type A376 in cell A1 of Sheet E, I would want cell A2 to pull through A377 from sheet C, A3 to pull through A378 from sheet C etc etc. Can this be done? Have I made any sense? Please help. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of lookup formula required
Hi Syndrome,
i almost crack my head! when you are in sheet E or wherever... you type A376 drag the right bottom corner of the cell downward you will get the series in sequence same as u did on other sheet. you got a nice alias... happy holidays and relax... "Syndrome" wrote: Let's say for eg I have 5 worksheets labelled A,B,C,D,E. Worksheet A has in column A the alpha-numerical values A1-A250 going down the page, with one value per row. Worksheet B has values A251-A500 again in column A going down the page, the same with sheet C & D up to the value A1000. I want to be able to type a value in cell A1 of worksheet E, and then rows 2-10 of the same column to return the succeeding values as per worksheets A,B,C,D. For eg if I were to type A376 in cell A1 of Sheet E, I would want cell A2 to pull through A377 from sheet C, A3 to pull through A378 from sheet C etc etc. Can this be done? Have I made any sense? Please help. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If / Lookup / Filter type formula ,..? | Excel Discussion (Misc queries) | |||
Maintain Formula Reference (sort of) | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |