Selecting content from one cell over another
Hi not sure where to start with this one
I have a range of cells where the dates may change. in the summary cell i want the most recent updated date. the cells are set up as following
Tittle Submission Original Submission New Actual Submission Date
Submission date 1/1/21 1/10/21 1/9/21
If no revised "new" dates have been set the cell would be blank along with the actual submission date
So really if date only in submission original, take that date, if date in submission new take that date, if date in actual submission take that date.
Look forward to you insights.
Best Answers
-
Thanks @Mike DeLuca, this helps a lot.
While I'm sure there are others who could offer a cleaner solution to the one below, I'm confident this should work: (where <>"" is another way of saying 'is not blank')
= IF ([Submission Actual]@row <>"", [Submission Actual]@row, IF([Submission Revised]@row <>"", [Submission Revised]@row, [Submission Original]@row))
This is working the IF formula backwards, looking for data in 'Actual' first, then 'Revised' and if both blank, 'Original' data.
Hopefully I've interpreted the request correctly, and hope this helps?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Answers
-
Hi Mike,
Maybe the MAX Function?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Hi Jason, have used the Max, but on rear occasions the actual maybe less than the revised date.
-
Hi Mike,
Am I right in understanding that the "actual date" is driving the response required?
In other words, if the "actual date" cell has a date, the formula should choose this date regardless of any of the other dates?
Am I also understanding that if there is no date in the "actual date" cell, to then choose the next most recent date from the other cells?
If so, I wonder if the formula goes something like this...
IF ([actual date cell] = "", MAX( range of related date cells), [actual date cell])
Hope this makes sense?
Looking forward to further clarification otherwise.
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
A better description would be.
If date in submission original this goes to = submission latest cell
If date in submission original & Submission revised, select submission revised = submission latest cell
If date in submission original & Submission revised & submission actual, select submission revised = submission latest cell.
The issues is sometimes the submission revised date may actually be less that the submission original. this is why the Max formulate is not always going to work for me.
Hopefully this is clearer than my first descritpion.
Regards Mike
-
Thanks @Mike DeLuca, this helps a lot.
While I'm sure there are others who could offer a cleaner solution to the one below, I'm confident this should work: (where <>"" is another way of saying 'is not blank')
= IF ([Submission Actual]@row <>"", [Submission Actual]@row, IF([Submission Revised]@row <>"", [Submission Revised]@row, [Submission Original]@row))
This is working the IF formula backwards, looking for data in 'Actual' first, then 'Revised' and if both blank, 'Original' data.
Hopefully I've interpreted the request correctly, and hope this helps?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Thanks @Jason Albrecht Seems to work a treat
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!