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...
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.
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.
mathetes Thank you that worked brilliantly