Make Worksheet Reference in a Formula Relative

D

Don Kirk

I have a worksheet where a number of cells get their value
from the previous workheet using the formula format:

='worksheet(Y)'!$X$N

When I make a copy of the worksheet and move it to the
end, the result in the new sheet refers to the original
sheet.

How can I make the formula automatically refer to the cell
in the preceeding sheet, as opposed to the original sheet?

I want to copy the sheet about 30 times in the same
workbook, and each new sheet needs to refer to the one
preceeding, not the original sheet.
 
H

Harlan Grove

...
...
How can I make the formula automatically refer to the cell
in the preceeding sheet, as opposed to the original sheet?
Click to expand...
...

You'd have to use VBA to implement a user-defined function to do this. See the
linked archived article for one possibility.

http://www.google.com/groups?selm=uNDmzFfUBHA.1216@tkmsftngp07

To pull the value of cell X99 in the preceding worksheet, you'd call this udf as

=showoff(X99,-1)

If you don't want to or can't use VBA, then the only alternative is crating a
list of worksheet names in order, perhaps named WSLst, and using it in INDIRECT
to adjust the worksheet reference. This is most efficiently done by also using
the defined name _WSName_ referring to

=MID(CELL("Filename",INDIRECT("A1")),
FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)

Then the value of cell X99 in the preceding worksheet would be given by

=INDIRECT("'"&INDEX(WSLst,MATCH(_WSName_,WSLst,0)-1)&"'!"&CELL("Address",X99))

The reason I'm using CELL("Address",X99) separately rather than including it in
the string literal as "'!X99", is that using the cell call allows you to copy
and paste cells containing this formula and have the cell address change as
expected.
 
D

donkirk

Thank you, thank you, thank you Harlan, for that code.

Never have done VBA previosly.... struggled a bit.... but has solved m
problem nicely

You are truly an Excel GOD!

Don Kir
 
Top