worksheet positioning for relational formula

If you changed the sheet numbering, given that you had a number on your sheets, then yeah. Otherwise it would take VBA and a UDF.

Here is a crude example. Note this does make use of the morefunc add-in SHEETNAME() function ...


=SUM(INDIRECT("'"&LEFT(SHEETNAME(),5)&--RIGHT(SHEETNAME(),1)-1&"'!a1:a3"))

This is also assuming that you will not be using this on sheet 1 and your sheets are named Sheet1, Sheet2, Sheet3, etc; and in order.

Bob, here should be a way with no VBA and no add-ins (if I didn't overlook a step)...

1. From the menu Insert Names Define...
2. In the Names in Workbook box type SheetPos and
3. In the Refers To box type Get.Document(87) -- note, it may help to hit the F2 key while in this box to put it in "edit" mode.
4. Click the [Add] button.
5. Back to the Names in Workbook box and type WSNames and
6. In the Refers To box type Get.Workbook(1)
7. Click the [Add] button and then the [OK] button.
8. On the sheet (sheet3 in your example) let's say cell A1 type the formula =INDIRECT("'" & INDEX(WSNames,SheetPos-1) & "'!A1") -- changing the "A1" in the formula to the address of whatever cell you had in mind.

Hiya Zack.

HTH

I tried Greg's approach, but ended up with a VALUE error.

I tested it on the following...

Sheet1, cell A1 = 100
Sheet2, cell A1 = 200
Sheet3, cell A1 = this formula: =INDIRECT("'" & INDEX(WSNames,SheetPos-1) & "'!A1")

When the sheets are in numerical order, I would expect Sheet3, cell A1 to reflect the value in Sheet2 cell A1, or 200. When I move Sheet3 to be positioned between Sheet1 and Sheet2, I would expect Sheet3, cell A1 to reflect the value in Sheet1, A1, or 100.

Any help as to why the formula for Sheet3, cell A1 would result in a VALUE error? (Please also refer to Greg's suggestion directly above this response.

Thanks for any help!

bobmc

I just realized that I didn't explicitely state that you need to put an equals sign in front of those two named formulas, i.e.
SheetPos refers to =Get.Document(87)
WSNames refers to =Get.Workbook(1)
I hope it's this simple... If it is, my apology for the oversight.

You're brilliant, Greg!

Thanks mucho!!!

bobmc