Adding a number to a cell every week

I have a spreed sheet that has a column A with numbers and every week these numbers need to be updated with a number. Is it possible to have this update it self every Friday.
e.g. A2 = 20 need to have 6 added every Friday for 12 months, A3 = 200 need to have 60 added every Friday for 6 months, A4 = 600 need to have 155 added every Friday for 18 months and so on. Thank you.
jodyreidIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
It is possible to write a VBA macro that could take some action every week at a particular time, but this feels pretty risky.  First the Excel file would have to be open at that exact moment, and not in active use by a user, as they could confuse the updates.  So if for some reason the file was closed at the weekly time, the update would not occur.  In theory you could add more complexity, and have some fail safe checking to know the next time the workbook is opened if any updates were missed, but that feels like it will be a bit tricky to perfect.

Why not just have someone click a button, or run a macro to do the updates once a week?  Presumably this workbook is being used by them so they should be right there.

The other approach is to try and do something off the Windows Task Scheduler, probably using a VBscript approach to update the workbook.  But it would have to not be open by a user at that time, or else the weekly update will fail.

It would take a bit of math, but another approach it rather than updating the values once a week, build a smart formula that looks at a base date that is set on the sheet, and then can calculate the current value for each of the cells you need updated.  Basic idea is it knows todays date, looks at the base date, determines how many weeks have gone by, and multiplies that by the adder for each cell to get the current value.

Jut some thoughts...


»bp
0
jodyreidIT ManagerAuthor Commented:
Yes a VBscript would work or a button that can be pressed every week would work. Thank you.
0
Bill PrewCommented:
Okay, good, I can work up a sample.  Just want to understand exactly what I think you meant.
e.g. A2 = 20 need to have 6 added every Friday for 12 months, A3 = 200 need to have 60 added every Friday for 6 months, A4 = 600 need to have 155 added every Friday for 18 months and so on.
So, some of the cells will have a value added to them each week, others may not, if a certain amount of time has gone by.  For example, based on what you described as a sample:

  • A2 = 20 need to have 6 added every Friday for 12 months
  • A3 = 200 need to have 60 added every Friday for 6 months
  • A4 = 600 need to have 155 added every Friday for 18 months

Then after 6 months we stop adding to A3, right?

If that's true then we will need a cell to store the date that we are calculating that from, sort of a "day 1" cell.  Is that what you imagined, or were you thinking of something else?

Also, how many of these cells will have their value changed each week, is it 2 or 3, or many?  I ask because there we will need to start the "rules" for each cell someplace for the macro to reference each week, so that it knows to change A2, how much to add to it, and for how many months.  If it's just a few and you don't need to change/maintain that then it could just be in the macro behind the scenes.  But if it's a lot then we might want to place it in another sheet with a row for each cell that we need changed, and what the rules are.


»bp
0
jodyreidIT ManagerAuthor Commented:
Yes that is correct. Thank you.
0
Bill PrewCommented:
Okay, just to see if we are on the same page here is a little proof of concept.  Notice there is a sheet where the data lives named "Data", and then a sheet where the rules are named "Control", which also contains the button that will be clicked once a week, and the important dates.

Give it a try, take a look at the code, poke at it however you want and see if this is what you need.

EE29086353.xlsm


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.