Obtain values based on 2 criteria in another row
Hi, I hope you guys can help me out,
For the sheet below I would like the Downtime Start Date and Start time to be copied from the "New Job" to the "Follow on Job"
IF
The Job number in the "Follow on" row is the same as the Autonumber of the "New Job" and for the values (Downtime Start date and Start Time) to be put in 2 new columns (not shown)
The formula at the moment is
=INDEX([Downtime Start Date]:[Downtime Start Date], MATCH([Job Number]@row, Autonumber:Autonumber))
But I am not sure if this is quite right as it seems to occasionally just leave the cell blank, I also want it conditional so it only performs this function if the row is marked as a "Follow On" job in the "Is Parent ?" Column
I am not sure this makes sense but I hope it does!
If you need anything clarifying let me know, any and all help appreciated
Answers
-
To take care of the occasional blank issue, include he zero in the final portion of the MATCH statement to indicate looking for an exact match.
=INDEX([Downtime Start Date]:[Downtime Start Date], MATCH([Job Number]@row, Autonumber:Autonumber, 0))
Which sheet has the checkbox on it?
-
Hi Paul,
Thanks for the reply, great steer on using the zero, thanks.......There is a checkbox is on the same sheet just not shown in the image but this is just to test my workflows, to mark as New or follow on Job I am using single select drop down.
-
Ok. Is the dropdown on the source sheet or is it on the sheet that we are pulling the data to?
-
Ah I see what you mean, its on this sheet (Column name Is Parent ?)
Having read my initial post I may have confused the issue, there is only a single sheet, I just want to copy the cell contents between cells on the same sheet but only when certain criteria have been met
-
Is the copy of the cell going to be on the same row as the cell you are wanting to copy from?
-
No and that's a big part of my problem,
As an example if we take the row marked as "New Job" (in red) The "Downtime Start Date" and "Downtime Start Time" cells are populated from a form which is fine.
However I would like this to also appear in the row below marked as the "Follow On" (in Green) but ONLY if the "Job Number" in this row is the same as the number in the Autonumber column of the row marked "New Job"
-
Ok. I'm starting to see what you are wanting now. We are going to have to use separate columns for this to avoid a circular reference or use a second sheet.
-
Okay great, either way would no pose an issue but additional columns would be preferable
-
In that case you would want to insert two new columns. One for the date and one for the time, and then use the INDEX/MATCH above.
-
Thanks Paul,
However I only want to perform this IF the "Job Number" in the row in question is the same as the Autonumber in the "New Job" row.
Can I make the Index match conditional?
-
Right. That's what the MATCH function is for. It finds where the Job Number on the current row matches in the Auto-number column.
-
Ah yes of course !! Thanks for that Paul much appreciated
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!