Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Automatic Formula Updates

0

Hello,

Am I able to set up a formula that auto-updates when I input data into a different cell?For example:
In the file I have uploaded, each week I input a new number. The formula that calculates WoW change is based upon the new weeks number I manually input. Is there a way that the formula auto-updates, so that the WoW percentage changes automatically, only when I input the new week number?

I would like the formula that generates 43%, (week47 over week46), to auto update when I input the number for week 48, (week48 over week47). Currently, I manually change the cell number within the formula after having input the new data.

The version of excel I use is Microsoft Office 365, Windows 10

Any help/advice is greatly appreciated.

excel
posted by: rockerkris873 on Tue Nov 30, 2021 at 11:53 am
Answer
Discuss

Discussion

Hi Kris and welcome to the Forum.

Are you saying you want the "updating" formula to be in the same cell? (Many report sheets instead have the week-on-week data in columns with the % change easily calculated below the data). If so, please EDIT your original question above (to clarify it for other contributors and users). Aslo it nearly always helps if you attach an Excel file (with test data) so we can better see what you're trying to do- you can do that using the Attach Files... button when you edit a question.

p.s. It also helps if you specify your Excel/ MS Office version (in your Profile) since some solutions won't work on older versions of Excel.
John_Ru (rep: 6142) Nov 30, '21 at 12:33 pm
Thanks John.
I've added a sample workbook, hopefully that clarifies somethings.
Addittionally, I added my excel version and operating unit.
rockerkris873 Nov 30, '21 at 2:47 pm
Add to Discussion

Answers

0

Kris

Given you have Excel365, in the revised workbook I've used some helper cells (A8:B10) to get a formula that you don't need to change each week and will update the value as the next value is typed in (assuming there was a value in the previous week's cell). The explanation is as follows

The column number of the last entry in B2:N2 is in cell B8 by this formula:

=LOOKUP(1,1/ISNUMBER(B2:N2),COLUMN(B2:N2))
Cell B9 then uses that column number (currently 9) to get the value in cell I2 by stating it in R1C1 format (rather than Excel's normal A1 style references, like H2)- where the FALSE parameter forces that in the INDIRECT function. That gives the Latest value using:
=INDIRECT("R2C"&B8, FALSE)
By taking 1 off that value (and so pointing to the column before that), the formula in B10 gets the Previous value using:
=INDIRECT("R2C"&B8-1, FALSE)
Then the formula in cell 02, your WoW (week-on week) percentage, is just:
=IFERROR((B9-B10)/B10,"")
and you don't need to change it. You only need to type in the next week's value in row 2

You might be tempted to try to combine those formula into a single complex formula in 02 but I'd suggest you either hide those rows or cut them and paste them in a new sheet (which you then hide).

Hope this makes sense/ works for you

posted by: John_Ru (rep: 6142) on Tue Nov 30, 2021 at 5:37 pm
Discuss

Discussion

Hi John,
Thanks for providing an example and the explanation to the formulas. I used the formulas have showed, and have it ALMOST working. The only problem I'm having now is the return numbers are 0 for "Latest Value" and "Previous Value". I've made sure that the formula you provided in your example matches to what my spreadsheet is, but for some reason the formula does not calculate correctly and only provides a 0.
rockerkris873 Dec 2, '21 at 11:46 am
Kris

Without your file, it's hard for me to guess where you've gone wrong!

If it has the same format as the example, is the range speciifed correctly in the "Last used column" formula and what is the value? (The correct column?)

If that's not the problem, have you  specified both the row and cell holding "Last used column" in the two bold bits below?
=INDIRECT("R2C"&B8, FALSE)
John_Ru (rep: 6142) Dec 2, '21 at 1:04 pm
Did you get my Answer to work for your case?
John_Ru (rep: 6142) Dec 7, '21 at 7:17 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login