worksheet positioning for relational formula

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
I have a workbook with several worksheets. I'd like to know if there's a way to have a formula in a given sheet (say sheet #3) refer to the contents of a cell in the sheet to its immediate left (sheet #2). If I move sheet #3 to be positioned between sheets 5 and 6, then the formula in sheet 3 would reference the cell in sheet 5.

Any help is greatly appreciated!

Thanks.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top