Refer to Previous or Next Sheet

B

build

G'day All,
Is it possible to refer to a cell on the previous sheet IN A FUNCTION
without refering to the sheet name?
Do sheets have index number or similar?

Thanking you in anticipation,
build
 
B

build

G'day,
Thank you for your reply.
That method requires the use of VBA (saw that in google), however I need to
do this in a function as some users are not permitted to open books with VBA
code. I did shout the stipulation "IN A FUNCTION". :) but thanks anyway.

cheers,
build
 
P

Peo Sjoblom

There is no way of doing this without VBA, at least not a generic way. One
option would be to name the sheets in a special way using numbers and have
them in number order then by first getting the sheet name of the sheet where
you are working

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

will return the sheet name of the sheet that holds the formula, note that
the workbook needs to be saved. Assume this formula is in a sheet named 3,
the previous sheet if in order would be 2, and by using a formula like

=INDIRECT("'"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)-1&"'!A1")

will return what's in A1 in a sheet named 2. Thus it is not possible to do
this in a generic way like JE's UDF but if you have some sort of index
number you can parse out those numbers and then subtract 1 to get the
previous sheet


--

Regards,

Peo Sjoblom








--

Regards,

Peo Sjoblom
 
Top