How do I create subsequent tabs that reference data from previous cells without having to manually change the formula?

Question

Hello all!

I am trying create a budget planner that will have tabs for the months. Each subsequent tab will reference the previous tab.

My issue is that when I create a new sheet (copy/move-create copy) everything stays the same. (I get it,... "Create a COPY"). What I would like for is that when I create a new sheet, the formulas are already there, sort of like the drag down option for formulas when inside of a cell, and they already reference the previous sheet.

What I mean by this goes something like this...i mean its just a rough example of it.

[Sheet 1]

Payment 1 - $100 | Payment 2 - $100

Debt Total =SUM((10000)-(Payment1+Payment2)

[Sheet 2]

Payment 1 - $100 | Payment 2 - $100

Debt Total =SUM((Sheet 1 Sum) - (Payment1+Payment 2)

[Sheet 3]

Payment 1 - $100 | Payment 2 - $100

Debt Total =SUM((Sheet 2 Sum) - (Payment1+Payment 2)

I'm already just creating a new sheet and manually changing the formula to reference the previous sheet's sum, but my question is, is there a way to be able to create the new sheet and having the formula already there?

Answer 1

Hi,

See if my solution here helps - Refer formulas to previous sheet (ashishmathur.com).

Answer 2

Hi UsernameNotTakenAF,

Thanks for contacting us and sorry for the inconveniences,

To achieve automatic referencing of the previous sheet in your budget planner, you can use a combination of cell references and the INDIRECT function in your formulas. The INDIRECT function allows you to indirectly reference another sheet based on a text string.

Here's an example of how you can set up your formulas:

1. In Sheet 1, you have your original formula:

    Debt Total =SUM((10000)-(Payment1+Payment2))
    ```

2. In Sheet 2, use the following formula for Debt Total:

```excel
    Debt Total =SUM((INDIRECT("Sheet1! DebtTotal"))-(Payment1+Payment2))
    ```

This formula uses `INDIRECT("Sheet1! DebtTotal")` to reference the Debt Total in Sheet 1.

3. In Sheet 3, use the following formula for Debt Total:

```excel
    Debt Total =SUM((INDIRECT("Sheet2! DebtTotal"))-(Payment1+Payment2))
    ```

This formula uses `INDIRECT("Sheet2! DebtTotal")` to reference the Debt Total in Sheet 2.

By using this approach, you can copy the entire sheet (including the formulas) and the references will automatically update to the previous sheet.

Please note that using too many `INDIRECT` functions can make your workbook complex and may impact performance, so use it judiciously. If you have a large number of sheets, you might want to consider using more advanced techniques like named ranges or a VBA (Visual Basic for Applications) solution.

I hope this helps!

Regards,
Sola