I would like to be able to add a tab and have a copy in a set of formulae and have the formula in a cell refer to the same cell locations in the worksheet to its left in the worksheet tabs.



Example: Worksheets (tabs) are Week1, Week2, etc. I want cell A1 in Week 2 to refer to cell A1 in Week1 and perform a simple subtract function. Then When I add in a new worksheet (tab) for Week 3 I want to be able to copy the formulas in the new worksheet and have cell A1 now refer to Week 2 for its function.

How do I get the sheet reference to be relational to a tab rather than an absolute worksheet name?

You can use the very old XL 4 macro functions in Defined Names.

Open Name Manager and press New. In the New name dialog write PrevShtValue in the Name field. In the Refers to field write the following:

=INDIRECT(INDEX(GET.WORKBOOK(1),GET.DOCUMENT(87)-1)&"!RC",0)

If you now enter =PrevShtValue in Sheet2!A1 it will return the value in Sheet1!A1

Alternatively you will have to use VBA.

Thanks, I tested that on a sample workbook and it works as described. I appreciate your assistance.

Can that macro function be revised to specify the cell in the Previous Sheet that is to be used in the new function?

For Example; in cell R1 of the new sheet I would like to perform an operation using the value contained in A1 of the previous sheet. How do I modify the macro to accomplish that?

It is possible.
See the attached.

Attached Files

PrevSht.xlsm (9.1 KB, 8 views)

Thanks you very much. This points to my need to spend some time with VBA.