Sep 01 2023 09:59 AM
Sep 01 2023 09:59 AM
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:
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.
Sep 02 2023 02:26 AM
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:
=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.
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.
Sep 02 2023 04:09 AM
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.