Application.caller is the cell that the formula is in.
Parent is the worksheet that holds that cell.
Next is the next sheet (that one is kinda obvious)
Now that you have the next sheet you can grab a cell value from it.

Application.volatile forces the formula to re-calc each time a calcualtion
runs (this is necessary as there are no precedents to the formula).

I personally would not use a formula like this as it changes as sheets are
moved, added or deleted which could make the results seem unpredicatable
especially since you can not readliy audit the formula to see what it is up
to... But to each his own.
--
HTH...

Jim Thomlinson


"w_c_mead" wrote:

Wow! Thanks for the info. That'll take me awhile to chew on...

- Bill

"Jim Thomlinson" wrote:

Take a look at this function. It returns the value from cell A1 of the next
sheet.

Public Function Test() As Variant
Application.Volatile

Test = Application.Caller.Parent.Next.Cells(1, 1)

End Function

If there is no next sheet then it returns #Value
--
HTH...

Jim Thomlinson