How to Drag cell/=code so that worksheet name (e.g., 1,2,etc) increases sequentially?

[Webem0ch]

Hello - I have an excel workbook within which I have named eac
worksheet numerically, i.e., 1,2,3,4,etc...

Is it possible to drag code so that the worksheet name wil
sequentially progress but that the cell will remain the same? Fo
example 1!$D$4 will upon dragging it become 2!$D$4?

Thank you in advance!

Michae

 

[Frank Kabel]

Hi
try the following
=INDIRECT("'Sheet" & ROW(1:1) & "'!D4")
and copy that down

 

[DNF Karran]

How about:

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 dow
so the values are the same all the way down. Indirect will then combin
these cells and return a cell value.

Dunca

 

[Webem0ch]

Dear Sirs,

I tried both formulas you've thoughtfully suggested yet the result fo
each is: #REF!

Further guidance is appreciated.

Michae

 

[DNF Karran]

Sounds like you might have missed the apostrophie's when using Frank
Example- which I forgot to put on mine!

They are required round the sheet name followed by a !

the correct formula for my example is:

=INDIRECT("*'*"&B1&"*'*"&!C1)

which is equivalent to:

=INDIRECT('1'!$A$1)

Dunca