Hi Ozgrid Fam,
I'm reaching out for some assistance to see how I can achieve the following:
The Action Column pulls in data from another tab.
How do I autopopulate Column M (New) with Todays date if Column I (Action) shows #N/A or 0, and Column E (Date) shows a date <= to today's date.
I've attached a spreadsheet for easier understanding.
I hope this is possible!
Thank you very much!
MK
Hello Marcel,
Attached is your test file ...:wink:
Let me know if you are getting the results you were expecting ...
Carim! Thank you very much! The formula worked!
Ultimately, i'm trying to combine your formula with a vlookup formula that is pulling in data into column M.
Is there a way to have a vlookup formula pull data, then once that data is pulled in, your formula would kick in and correct some of the entries in column M?
You're a rockstar!
MK
Glad the formula is what you initially needed ...:wink:
Regarding your second question ... could you please post a test file to precisely show what you are expecting ... :smile:
Carim,
Please see the attached spreadsheet.
This is a circular reference and I've changed the setting to accommodate but still have had no luck.
The result should be today's date in column M2 if the formula is working right.
Thank you so much for looking into it!
MK
Thanks for the new spreadsheet ...
Can you explain if there is a fourth condition to be added to the existing 3 conditions ...
or is there one of the 3 conditions which is replaced by you Vlookup() ....
There is no 4th condition.
Mainly, The vlookup will pull in any matches from the Yesterday column. For all entries that show up as 0 or N/A and are < or = to today's date in the Date Column, the formula you created would either turn all vlookup entries in the M2 column into Today's date, or leave the results from the Vlookup as is in the M2 column.
Hello,
Not sure to fully understand your explanation ...
My understanding is that the Column I ( Action ) which needs to be considered ... is in fact ... located on another sheet ...' Yesterday' ...
If this is correct ... attached is your test file Version 2 ...
Hope this will help
Hi Carim,
I've posted a spreadsheet to make it more clear what the objective is:
So column M has a Vlookup formula in Column M (New). The objective is to use a replace that vlookup with a vlookup formula that not only pulls data from the "Yesterday Tab" but also replaces all "#N/A" and "0" values with Today's date while keeping all the other entries in Column M constant if they are not "#N/A" or "0".
Does this make any more sense? Please let me know and i'll do my best to explain better!
Thank you again!!!
MK
Hello Marcel,
Attached is your test file Version 3 ...
Hope this proposal is in line with your expectation ...:wink:
Carim! This worked exactly how I've been trying to get it to work!!!
The only thing that was missing is adding this condition: AND(E2<=TODAY() to your formula of: =IF(ISNA(INDIRECT("Yesterday!M"&MATCH(A2,Yesterday!$A:$A,0))),TODAY(),IF(INDIRECT("Yesterday!M"&MATCH(A2,Yesterday!$A:$A,0))=0,TODAY(),INDIRECT("Yesterday!M"&MATCH(A2,Yesterday!$A:$A,0))))
You added it in your previous attempt seen below:
=IF(AND[SIZE=16px](E2<=TODAY()*[/SIZE]ISNA(INDIRECT("Yesterday!I"&MATCH(A2,Yesterday!A:A,0)))),TODAY(),IF(AND(INDIRECT("Yesterday!I"&MATCH(A2,Yesterday!A:A,0))=0,E2<=TODAY()),TODAY(),""))
So the formula would keep all pulled in entries if the date in E2 is not Today's date or Older (Before Today's Date)
How do I add this last condition?
This is the last element to complete the entire formula!
You're a master at this!
MK
Hello again,
Is the Version 4 ... now fully OK ...? :wink:
Carim! You are the man!!! This is exactly what I've been trying to do! You're a rockstar!
Thank you thank you thank you!!!
I can't thank you enough for taking the time and making this possible.
Really appreciate all your help!
MK
Very glad to hear you have totally fixed your problem ...:wink:
Thanks a lot ... for your Thanks AND for the Like ...:smile:
Don’t have an account yet? Register yourself now and be a part of our community!
