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

W

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
 
F

Frank Kabel

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

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
 
W

Webem0ch

Dear Sirs,

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

Further guidance is appreciated.

Michae
 
D

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
 
Top