Hi to all
if yes in column O or column P put the modification date in column Q - it must remain fixed
attached example
Hi to all
if yes in column O or column P put the modification date in column Q - it must remain fixed
attached example
Last edited by xam99; 01-17-2021 at 05:41 PM.
If I understand what you are trying to do, that sounds like you are trying to enter a time stamp. If I've understood correctly and you are allowed to enable iteration in your spreadsheet(s), then try this tutorial: https://chandoo.org/wp/timestamps-excel-formula-help/
Originally Posted by shg
The only other alternative is to use a Worksheet Change Event handler monitoring columns O and P.
Are you ok with a VBA solution?
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi to all,
ok tms
mrshorty
i tried the formula as in the link but it gives circular reference
Please Login or Register to view this content.
and that's why you need to "enable iteration in your spreadsheet(s)".i tried the formula as in the link but it gives circular reference
Code option:
Please Login or Register to view this content.
Hi tms, thanks you.
The macro work well, unfortunately the word "yes" is inserted by a VLOOKUP formula and the date is not inserted
if I enter "yes" by hand it works
Provided the cells in columns O:P are dependent on other cells on the same sheet this should work.
Please Login or Register to view this content.
Last macro doesn't work, even with yes entered by hand
Did you note:Post a sample workbook. See the yellow banner.Provided the cells in columns O:P are dependent on other cells on the same sheet this should work.
HI
here example
I don't know if the reason you did not enable iteration is because you were not allowed to or if you just overlooked that requirement. Assuming it was just overlooked, here's what I did (using Chandoo's circular reference formula method):
1) The condition is not only a single cell, but 2 cells, so the simple if is blank check (outer if()) will not work. I need a way to check if either (or both) of two cells contain the word "si". A COUNTIFS() should work for this COUNTIFS(O5:P5,"si").
2) The rest should be the same as in Chandoo's tutorial =IF(COUNTIFS(O5:P5,"si")>0,IF(Q5="",NOW(),Q5),"").
As you enter values in Foglio2, it will add time stamps in column Q for when the word "si" was first entered into D or E of each row.
Will that work, or would you prefer the VBA approach because you are not allowed to enable iteration?
Hi mrshorty
if active
Activate Iterative Calculation
(Attiva Calcolo Iterativo)
then it will work for all workbooks
it is better maybe vba
Last edited by xam99; 01-18-2021 at 04:50 PM.
I don't know that VBA is better or not -- it really depends on whether or not you are comfortable having iteration enabled. The main downside that I have seen from having iteration enabled is that Excel will not give you warnings when you accidentally make a circular reference. In my experience, it really depends on how important those circular reference warnings are to you.
I am not as experienced with creating these kind of VBA procedures as TMS and others. Perhaps something like what TMS has already proposed, but moved up to a workbook.sheetchange event procedure (https://docs.microsoft.com/en-us/off...ok.sheetchange )? Or maybe change it to a .calculate event procedure (that probably needs some place to store the previous state of Foglio1 and/or Foglio2).
I don't know which approach is better. How would you like to proceed?
Since then the workbook will be used by other colleagues where I work I would not have any problems.
I think it is better vba
HI to all,
i posted this thread in other forum
https://www.mrexcel.com/board/thread...-fixed.1158917
Last edited by xam99; 01-19-2021 at 11:27 AM.
How well did you understand what TMS did? Did you read the help file for the workbook.sheetchange event?
I created a workbook.sheet change event in the ThisWorkbook module, copied TMS's code (without the Sub/End Sub statements) into this procedure. Added "If sh.name<>"Foglio2" then exit sub" statement, a few other minor modifications, and it seems to work.Does that work?Please Login or Register to view this content.
Hi mrshorty
i put the macro in thisworbook but it doesn't work
Adjusted to work for "yes" or "si"
Please Login or Register to view this content.
Hi tms
now it works perfectly
thanks you
Hi tms the above macro works on a workbook and is fine
Now there is a problem for another macro that inserts the date into a cell
The post#7 macro is perfect:
the problem that "yes" is entered not by hand, not by formulaPlease Login or Register to view this content.
but it is inserted with another macro and this macro does not insert the date
Last edited by xam99; 01-19-2021 at 04:45 PM.
Entering a value with VBA is the same as entering it manually. The code in post #20 only works for "yes" and it needs to be in the worksheet where the changes occur.
The alternative code provided by Alan and amended by me only works for changes made to Foglio2 and updates Foglio1. It works for "yes" and "si" but nothing else.
The macro in post#7 works fine but YES or NO is entered
with a macro with this function
Please Login or Register to view this content.
Try this then:
Please Login or Register to view this content.
Note that my code excludes any changes other than those in columns O:P. Your code examples are changing column 10, which is column J.
Hi
this is the position of the cells in the new workbook, only colums J
however the "YES" is inserted by the post#24 functionPlease Login or Register to view this content.
perhaps for the new macro does not recognize
Worksheets("ordina_mail_manuale").Cells(ultimariga, 10).Value = "SI"
Last edited by xam99; 01-19-2021 at 05:37 PM.
So we have two macros trying to run here -- one that makes changes to one tab and we are trying to come up with a second macro that detects the change made by the first macro and makes another change (adds date stamp). Am I understanding correctly?
Is there some reason you cannot have the first macro simply write the date stamp into the appropriate cell when it makes the change (and a second macro is not even needed)? It seems like it should be as easy as following the statement(s) in post 24 with something like thisworkbook.sheets(??).Cells(??,??).value=dateIt seems to me that if macro 1 is making the Yes/No/Si changes, have it make the date stamp change as well.Please Login or Register to view this content.
HI mrshorty now works
with the TMS macroPlease Login or Register to view this content.
thanks to TMS and mrshorty
a greeting
xam
You're welcome.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks