Refer to Previous or Next Sheet

  • Thread starter Thread starter build
  • Start date Start date
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
 
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
 
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
 
Back
Top