Make Value Static from Formula Once Date Reached

[rainwilds]

Hi there,

First time posting after reading lots of great threads.

I'm trying to work out how I can stop a cell from updating/calculating once a certain date is reached. The last value before that date is the value that should remain in-place. Is this possible?

So for example, A1 contains "=A2+1000" which equals 2000 (as A2 is 1000). A2 varies based on a formula; so A1 is always changing. At X given date, the value in A1 freezes to remain permanent/static.

Would really appreciate any help thank you!

David

[Richard Buttrey]

You'll need a macro to convert a formula to an absolute value once a condition is reached.

For this to happen a suitable event needs to be used. The obvious choice would seem to be a Workbook Open event so that when you opne the workbook if the condition is met the A1 formula is converted to a value.

Please Login or Register  to view this content.

Adjust the Sheet1 reference as necessary. Note this is the VBA sheet CODE Name not the Sheet1 TAB name

[Shareez Saleem]

Please have a look here.
If you are trying something else, please upload a sample sheet.

[rainwilds]

Thanks Richard for the reply.

I think it looks likely that I'll need a VBA solution to this problem. I can see what you mean about creating an event trigger - opening the workbook seems like the most logical event. I'm just not familiar with VBA language. I'd attach a screenshot to further explain the issue I'm but can't yet as I don't have enough posts on the forum yet to upload images.

How would I set the Date in the VBA where B1 = Date? Or is Date VBA language for today's date?

Secondly, because the formula getting converted to a static value is within a table; the rest of the column with the same scenario would need to be updated to static also. That is column A for example all need updating. I'm using structured data in all of the formulas; Can VBA address table structured data?

Hopefully there's not too many questions there!

[rainwilds]

Thanks Shareez for the making the sample sheet. Quite powerful and does work nicely. Unfortunately it requires a table with a range of dates to test against in the VLOOKUP. Unless I've miss understood the sheet you uploaded, in my case I don't have a table of dates to check against for a value. The value is dynamic based on a numeric formula unrelated to dates. So I don't have a table of any data to lookup; hence why I don't think VLOOKUP would work.

But I could be mistaken!

[AliGW]

Please have a look here.
If you are trying something else, please upload a sample sheet.

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I am sure you have been alerted to this expectation before ...

[Richard Buttrey]

Exactly right.
'Date' is a VBA reserved word for today's date - or more precisely the WIndows/Mac system date.

Not sure what you mean by structured data - would you upload the workbook or at least a reresentative subset and explain further.

The code would be slightly modified to

Please Login or Register  to view this content.

[rainwilds]

Thanks Richard. I'm pretty excited with what VBA can do! Just reserved two books from the library. I've been stumped for hours trying to find a formula to convert values to static other then using a manual Paste Special. By structured data, I mean, structured references (sorry for the terminology confusion). This is where tables in Excel can be referenced using particular code like Expenses[[#Totals],[MyColumn]] for the total of MyColumn.

As it relates to this scenario, I need values in MyColmn to take on those of the adjacent column but only as static not referenced too them.

I've just created a small scaled down scenario using a blank worksheet. Basically there's a small table with two columns. One column contains values that vary based on RAND(). The other column is empty awaiting the Static Date to copy the static last values from the first column. Finally, there's the Arbitrary Date; which is just an input date to test it all with.

I'll go test the VBA code you posted above, thanks Richard.

UPDATE: The forum won't let me upload the spreadsheet yet. Still says I haven't posted enough. :/

[Richard Buttrey]

I'll go test the VBA code you posted above, thanks Richard.

UPDATE: The forum won't let me upload the spreadsheet yet. Still says I haven't posted enough. :/

Must admit I've only become aware of this limit recently, its never entered my consciousness before. I suspect it's a fairly small limit like 5 posts so once you've made another post you'll probably be able to.

Yes, you're in for an exciting ride with VBA. I'm constantly amazed at the amount that can be done with very few lines of VBA code.

[rainwilds]

The code you posted above is perfect, except it won't seem to run when opening the file; I have to force it by running from the VBA editor. Not sure why as I said to trust the macro document.

The next hurdle is to apply this exact code (with range differences) to other columns, only entering the loop if the cell/column does not already contain a value. So I need to find columns to see if their value is blank (ignoring those that aren't), then enter into the loop to test/apply the static values (as previous).

What would be the best testing code to find the columns? What I know is the names of the columns specifically like this "Rem. JUL19 C2" and "Rem. AUG19 C2"; where only the month JUL/AUG/SEP etc. is changing.

Thanks for all the help Richard.

David

[rainwilds]

UPDATE: Having a little play with the macro editor it seems too of grabbed the structured references of the table which is great. So I don't need to worry about missing any rows in the range selection if new rows are added to the table.

Please Login or Register  to view this content.

[Richard Buttrey]

The code you posted above is perfect, except it won't seem to run when opening the file; I have to force it by running from the VBA editor. Not sure why as I said to trust the macro document.

The next hurdle is to apply this exact code (with range differences) to other columns, only entering the loop if the cell/column does not already contain a value. So I need to find columns to see if their value is blank (ignoring those that aren't), then enter into the loop to test/apply the static values (as previous).

What would be the best testing code to find the columns? What I know is the names of the columns specifically like this "Rem. JUL19 C2" and "Rem. AUG19 C2"; where only the month JUL/AUG/SEP etc. is changing.

Thanks for all the help Richard.

David

Did you put the code in the Workbook Open Event. In the VBA you need to double click the Workbook object in the VBA Project Window list of objects (sheets, workbook..etc and select the let hand drop down to select the Workbook object and then the Open event from the right hand drop down.

The approach I always adopt for checking whether a particular column (or row) is blank is to use a helper row of cells above the columns of data and in that cell use a function to identify whether the range of cells is blank. Typically that would be something like

Please Login or Register  to view this content.

which will return 0 if the range is all blanks. Then in the loop the macro just needs to check the row of helper cells. That said would it really matter of you copied and pasted blank cells anyway. I agree you'd be processing some columns that aren't really neceesary but I suspec the very small time overhead would not be noticeable.

[rainwilds]

Thanks again for the feedback Richard. I like that clever helper row idea for loops. I did end up getting the Workbook_Open even to work. In the end after playing with VBA for a while I've got it working by testing a given date value and if the first in the column cell is blank. If true it calls the relevant macro to copy-paste the static values. Works a charm! You've been a great help thank you!

Please Login or Register  to view this content.

Please Login or Register  to view this content.