Updating a formula over 12 worksheets using Data from the previous worksheet

[MarmonDH735]

I have a problem. I am working on an inventory map for the whole year gathering incoming and outgoing data daily on each worksheet. 12 worksheets- 1 for each month Jan.- Dec. I have a formula that I copied to each of the but I need them to update instead of me manually changing each formula every work sheet. 

the formula: if we are on the August worksheet it would be:

=July!C1+Aug!C3-Aug!C4

Can I automatically update the next worksheet formula to =Aug!C1+Sept!C3-Sept!C4.

I have 10 different formulas like this in each worksheet and it’s time consuming.

I’ve looked everywhere and couldn’t find a solution!

Thanks in advance.

[#2]

@MarmonDH735 

You can achieve this by using structured references and the INDIRECT function in Excel. Structured references allow you to reference tables by name, and the INDIRECT function allows you to create a reference to a cell using text, which can be manipulated to refer to different worksheets.

Here is how you can set it up:

1. Table Setup: Ensure that your data is structured in tables. Each month's data should be in a separate table with a meaningful name (e.g., "Jan," "Feb," etc.).
2. Cell References: For your formulas, use structured references to refer to the tables. For example, if you have a table named "Jan" and you want to reference cell C1 in that table, you can use the structured reference Jan[C1].
3. INDIRECT Function: In your formulas, you can use the INDIRECT function to dynamically build the references to the previous and next month's tables. For example, if you want to calculate a value for August based on the previous month's data (July) and the next month's data (September), you can use a formula like this in the August worksheet:

=INDIRECT(TEXT(TODAY(),"mmm") & "![C1]") + INDIRECT(TEXT(DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1),"mmm") & "![C3]") - INDIRECT(TEXT(DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1),"mmm") & "![C4]")

This formula uses the TEXT function to create text references to the previous month (e.g., "Jul") and the next month (e.g., "Sep"). It then uses INDIRECT to convert these text references into actual cell references.

1. AutoFill: Once you have set up the formula in the August worksheet, you can use the AutoFill handle (the small square at the bottom right corner of the selected cell) to copy the formula to the other months. Excel should automatically adjust the references based on the relative positions of the tables.
2. Check and Adjust: After using AutoFill, double-check the formulas in each worksheet to ensure that the cell references are correct. Excel should have adjusted them correctly, but it is a good practice to verify.

By using this approach, you can create dynamic formulas that automatically update based on the current month and the tables' structured references, saving you the manual effort of changing the formulas in each worksheet.The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

@MarmonDH735 

In general it's best practice to keep all raw data in one sheet with month/date field for each item. From here you may build your monthly analytics, whatever. Otherwise that's INDIRECT. You may keep in some cell the name of previous sheet. Poor =INDIRECT(A1 & "!A3") won't give related references, perhaps you need to add combination with ADDRESS, depends on how you concrete formulae are located. Plus that's poor performance, each of such formula will be recalculated with every change in any sheet.

IMHO, to create once 10 formulae in each of sheet is less time consuming.