Forum Discussion
Solved
Automatically update formulas/ VBA
I am trying to find a way in which I can add a new worksheet each month or every two months, to a workbook, and the formulas will automatically update to the previous worksheet.
For example I have a Jan 2023 worksheet but then add March 2023- I want to following formulas if possible to update to the 'Jan 2023' cells not the 'master' cells. Without me having to manually change them.
=SUM(K32-Master!K32) would now need to be =SUM(K32-'Jan 2023'!K32)
Is this possible to do using a macro or VBA.
Thank you. That helps. I still have another question or two.
- I gather there are multiple formulas that need to be able to be modified each month, not just the single formula that you're displaying; is that correct?
- Did you take a look at the INDIRECT function via the hyperlink provided? If so, did it seem at all relevant? (See below; I've created a file to demonstrate how that might work, if I've understood your need accurately.)
- If INDIRECT doesn't work, or if you want further help, since that screen shot is of a workbook that does exist, I'm hoping you'd be willing to post a copy of the actual workbook on OneDrive or GoogleDrive, followed by pasting a link here in the forum granting edit access. That is the best way to help us (me and any others here who seek to answer questions such as yours) help you. Otherwise, we'd need to re-create a semblance of what you've got, which would never be quite as accurate or complete as the real thing.
On the chance that INDIRECT will do for you what you want, I'm attaching (below this message box) a sample of a workbook with four tabs in it, with each of the three that follow "Master" using INDIRECT to subtract that period's number from the starting number in the previous sheet. They do so by means of INDIRECT which references cell J2, off to the side, where I place the name of the previous tab. Note, by the way, that I use an underscore connecting the month name with the year (rather than a space). Here's a screen shot for what is in May_2023, which references Mar_2023 by means of INDIRECT and the reference in cell J2.
Your description, even with the example, is not clear enough to be sure exactly what you want. The fact that you speak somewhat ambiguously about updating by adding a new worksheet, and I quote, "each month or every two months" doesn't help with the kind of clarity you need for reliable and solid spreadsheet design.
So I'll offer the suggestion that you research the INDIRECT function as one possible solution for the need you appear to be describing.
But I'd also offer the suggestion that, depending on what this workbook is about, it might even be a more solid and reliable design if you did NOT add a new worksheet every month or two. Excel is strong in taking single databases consisting in many months of transactions, and analyzing them, extracting monthly data, etc. We often come into designing an Excel spreadsheet with a mindset accustomed to paper ledger sheets where monthly sheets made a lot of sense; that physical separation is clearer to the human reader. Excel isn't limited in that way.
So if you're open to re-thinking how you've designed this workbook in the first place, come back with some more info on what kind of monthly information you're storing, what you do with it...why you have designed it with monthly or bi-monthly (or even other "new" sheet frequencies)......
Thank you for your reply. So the workbook is for construction progress claims, which have to be submitted each month. However if we do not attend the job then a month would be missed. These jobs take an undetermined number of months or years so I can't set up a master workbook with a set amount of worksheets.
What I am hoping to do is pull previous claimed amounts from the previous month to reflect on the current month claim. I have included a screen shot of my current workbook for you to see. The formula which is displayed relates to "Contract works claimed this month".
I am hoping that I can create a workbook which then can be semi- locked and used by other users. These other users would then create a copy of the previous months claim and rename it to the current month. When they do this I would then like to have a macro or a function which would allow for the formulas to update automatically to the reflect the previous month. ie. my previous example.
Thank you. That helps. I still have another question or two.
- I gather there are multiple formulas that need to be able to be modified each month, not just the single formula that you're displaying; is that correct?
- Did you take a look at the INDIRECT function via the hyperlink provided? If so, did it seem at all relevant? (See below; I've created a file to demonstrate how that might work, if I've understood your need accurately.)
- If INDIRECT doesn't work, or if you want further help, since that screen shot is of a workbook that does exist, I'm hoping you'd be willing to post a copy of the actual workbook on OneDrive or GoogleDrive, followed by pasting a link here in the forum granting edit access. That is the best way to help us (me and any others here who seek to answer questions such as yours) help you. Otherwise, we'd need to re-create a semblance of what you've got, which would never be quite as accurate or complete as the real thing.
On the chance that INDIRECT will do for you what you want, I'm attaching (below this message box) a sample of a workbook with four tabs in it, with each of the three that follow "Master" using INDIRECT to subtract that period's number from the starting number in the previous sheet. They do so by means of INDIRECT which references cell J2, off to the side, where I place the name of the previous tab. Note, by the way, that I use an underscore connecting the month name with the year (rather than a space). Here's a screen shot for what is in May_2023, which references Mar_2023 by means of INDIRECT and the reference in cell J2.
