Some sort of lookup formula required

[Guest]

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

 

[Guest]

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

 

[Bernard Liengme]

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 Liengme]

I blush when comparing this to my solution!
But I assumed his data was not really A+number

 

[Aqib Rizvi]

CRL
I tried to understand the formula and created worksheets, but it gives
Zero in the results.
Aqib

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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...