Dragging a cell/formula that will advance the (numerically named) worksheet names

W

Webem0ch

Hi - Is is somehow possible to drag the VLOOKUP formula shown belo
whereby only the worksheet name will advance by one. My worksheets ar
numerically named (i.e., 1, 2, 3, etc. . .)

=VLOOKUP(A2,'1'!$A$3:$B$3,2,FALSE)

If the VLOOKUP formula will not work, I would apprecaite other idea
about how to approach this. I am looking for a formula that whe
dragged will advance only the worksheet name by 1 - and this is why
have my worksheets numerically named.

Advance Thanks,

Michae
 
J

JE McGimpsey

One way:

Assuming the formula is entered in Row 2:

=VLOOKUP(A2,INDIRECT(ROW()-1 & "!A3:B3"),2,FALSE)

although I question your range $A$3:$B$3. Do you really want to look up
a value on one row?
 
W

Webem0ch

If a solution better than VLOOKUP exists I am all for it. I tried
other formulas approaches such as:

Approach 1)
=INDIRECT("'Sheet" & ROW(1:1) & "'!D4")

Approach 2)
Col a Col b Col c
=INDIRECT(B1&C1) 1 !$A$1
If you drag col b down so it counts sequentially then copy col c down
so the values are the same all the way down. Indirect will then combine
these cells and return a cell value.

I tried both the above formulas the result for each being: #REF!

Thanks
 
W

Webem0ch

Dear JE McGimpsey,

I played around with the formula you sent and found a way around th
VLOOKUP function:

=INDIRECT(ROW()-1&"!B3")

I know precisely where the cells are on each worksheet page and th
VLOOKUP was not necessary, yet I was scrambling to get something t
work.

Thank you!

Michae
 
P

Peo Sjoblom

J.E.s formula works for me if I change lookup value A2 to $A$2
unless you want A2 to change to A3 etc.?

What's puzzling to me is that you don't seem to refer to his formula
in your answer?
 
Top