Refer to Previous or Next Sheet
 

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

Refer to Previous or Next Sheet
 

See

http://www.mcgimpsey.com/excel/udfs/prevsheet.html

In article ,
"build" wrote:

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

Refer to Previous or Next Sheet
 

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


"JE McGimpsey" wrote in message
...
See

http://www.mcgimpsey.com/excel/udfs/prevsheet.html

In article ,
"build" wrote:

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

Refer to Previous or Next Sheet
 

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





"build" wrote in message
...
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


"JE McGimpsey" wrote in message
...
See

http://www.mcgimpsey.com/excel/udfs/prevsheet.html

In article ,
"build" wrote:

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