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

Unique excel formula solution

0

Hi,

Is there any excel formula or any other solution in excel for when the formula based cell value changes from 0 to 1, i want to keep the cell value to 1 before it again change to 0.

For example, Say the formula based cell value is 0, than the formula based cell value changes to 1. Want to record the cell value of 1, before it changes to 0.

Attached excel sheet with explaination in Remarks column

Thanks

excel
MS Office Version: 2016
posted by: SunilA (rep: 58) on Tue Mar 26, 2024 at 7:04 am
Answer
Discuss

Discussion

Sunil

Sorry but I don't know what you mean. Please expand your question to explain why you want to do whatever it is and if possible attach an Excel file showing the cell/ formula.
John_Ru (rep: 6142) Mar 26, '24 at 10:29 am
Agreed, more info is needed.
Are 1 and 0 the only values the cell will have?
If the cell value is 0 and changes to 1 do you want to do anything? (record the 0 value somewhere?)
A sample file will be most helpful.
WillieD24 (rep: 557) Mar 26, '24 at 1:21 pm
Yes, 1 and 0 is the only values the cell have.
I want to record the value 1 somewhere, before the cell value changes from 1 to 0. Attached excel sheet
SunilA (rep: 58) Mar 26, '24 at 2:29 pm
Sunil. You want to record the (transitory) 1 value but don't you also want to timestamp when that happened? Otherwise (in time).you coud just get a column of current Os and another of past 1s.

I think I've provided you with enough solutions like this (so you should have learned the basics of how to do this). I'm too busy to do mopre on this today so I hope Willie has some spare time to reply.
John_Ru (rep: 6142) Mar 27, '24 at 4:16 am
Sunil,

Have you looked at the VBA solution I provided? Please comment.
WillieD24 (rep: 557) Mar 30, '24 at 12:38 pm
Add to Discussion

Answers

0
Selected Answer

Sunil

In the cell where you want to record (let's say C3) the value of the cell (let's say A3) with either 1 or 0 enter this formula in C3: "=IF(A3=1, 1, "")". Now if A3 value is 1 C3 will show 1; if A3 value is 0 C3 will be empty. If you want to capture the date or date and time when the value changes you can use in cell D3 either TODAY() for date or NOW() for date and time. But these functions are volatile and recalculate every time there is a change in the worksheet. One way to make this static is to use data validation. In another cell (let's say C1) enter either TODAY() or NOW(). In the cell where you want to capture the date  and time (let's say D3) create data validation – select from list and make the cell with the function (C1)  the source cell. The date and time in D3 will now be static but this will mean the user needs to select the data validation to change the date and time.

See attached file.

If this is the solution you hoped for please mark my answer as Selected.

A VBA solution is also possible but will require more time on my part. If you would prefer a VBA solution let me know.

UPDATE: Mar. 28/24

Attched file with a VBA solution (Record Cell value - Rev2-VBA)

This file has the original formulas on Sheet1 to capture the values from Sheet2. Sheet2 has a Worksheet_Change event which is triggered when a cell in Col-A changes. This event code then calls another macro to make the changes on Sheet1. If the new Sheet2 value is 0, then only the Sheet1 Col-A value is changed. If the new value on Sheet2 is 1 then on Sheet1 Col-A changes, 1 is entered to Col-C, and the date/time is entered to Col-D. Because the date/time is entered by the macro it is static and doesn't change until the next time 1 is entered into Col-A.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Wed Mar 27, 2024 at 12:35 pm
Discuss

Discussion

@Willie - neat idea and not one I'd encountered before. It certainly fixes the time that the source cell last went to 1 but doesn't D3 always show a date rather than  a blank (to indicate it's yet to get to 1)?

I'll leave this to you but just as a word of caution it's often tricky to know what Sunil wants and it can mutate when you ask basic questions (so become quite time consuming). 
John_Ru (rep: 6142) Mar 27, '24 at 6:59 pm
@John
Yes, you are correct about D3. I have changed the data validation option to include "~ ~ ~" as an option to choose when the cell ( C1 ) is zero. The attached file has also been updated.
I know what you mean about working with Sunil. I have seen that happen in several earlier posts. Time will tell how this one plays out.
WillieD24 (rep: 557) Mar 27, '24 at 7:55 pm
The excel is formula based and no manual intervention.
For example, At start the cell value is 0 (Column A), than whenever the cell value changes to 1 (Column A). the value 1 need to record somewhere let say in Column C, but whenever the cell value again changes from 1 to 0 (Column A) than the recorded cell value in Column C should not change, It should freeze to 1 alongwith timestamp.
You can also provide the VBA solution.
Thanks
SunilA (rep: 58) Mar 28, '24 at 12:52 am
@Sunil

I have just added a second file to my answer - Record Cell value - Rev2-VBA. This second file needs no user intervention to update Col-C and Col-D. Sheet2 Col-A still requires a user to change the value to 1 or 0. I would also suggest you add some protection to Sheet1 so the formulas in Col-A don't get deleted or corrupted.

If this is what you were hoping for please mark my answer as Selected and add to these comments to indicate which file you prefer.

Cheers   :-)
WillieD24 (rep: 557) Mar 28, '24 at 10:17 pm
@Willie- your VBA solution seems to work well (but don't be surprised if Sunil says that the changes to sheet 2 aren't made manually but it links to another remote workbook, website or SQL query).

Once again you've provided a solution but not explained your workings in the Answer (please try to do so in future). Remember that we ask users not to hide their questions/ requirements in their file and if you look under the Help section above (next to Rules) you'll see:

Answer Etiquette
 ....
If you include a file in the answer, explain what you did in it within the answer itself so that future readers can more easily see the answer.

 Remember, the goal is to help the person who asked the question, but also anyone else who comes to the page looking for an answer.
John_Ru (rep: 6142) Mar 29, '24 at 3:55 am
@John
My appologies for that. I felt there was enough info in the discussion points. I have added the details about the VBA file in my answer above.
I try to not get too wordy, and if someone else thinks the answer could solve thier issue, they can download the file. I will try to remember to give fuller explanations in the future.
As for Sheet2 Col-A changing by some remote means, the Sheet2 Worksheet_Change event is still triggered. 
:-)
WillieD24 (rep: 557) Mar 29, '24 at 9:16 am
@Willie - understood and thanks. I'm mobile now so can't check but I think if you point a cell in Sheet2 to one in another open workbook (and change that) it doesn't trigger the change event but does trigger the Calculate event.
John_Ru (rep: 6142) Mar 29, '24 at 10:55 am
@John
The tesing I did was to have Sheet2 Col-A capture the Sheet3 Col-A value (in the same manner as Sheet1 Col-A catures Sheet2 Col-A value). When the Sheet3 value was changed the Sheet2 Worksheet_Change event was triggered.
:-)
WillieD24 (rep: 557) Mar 29, '24 at 11:27 am
@Sunil

Thanks for selecting my answer.
WillieD24 (rep: 557) Apr 6, '24 at 5:13 pm
Thank you for all your valuable expert input and solution. Very much appreciated.
SunilA (rep: 58) Apr 10, '24 at 1:38 pm
Add to Discussion


Answer the Question

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