Obtain values based on 2 criteria in another row

Dan Crimmins

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

Paul Newcome

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?

Dan Crimmins

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.

Paul Newcome

Ok. Is the dropdown on the source sheet or is it on the sheet that we are pulling the data to?

Dan Crimmins

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

Paul Newcome

Is the copy of the cell going to be on the same row as the cell you are wanting to copy from?

Dan Crimmins

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"

Paul Newcome

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.

Dan Crimmins

Okay great, either way would no pose an issue but additional columns would be preferable

Paul Newcome

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.

Dan Crimmins

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?

Paul Newcome

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.

Dan Crimmins

Ah yes of course !! Thanks for that Paul much appreciated

Paul Newcome

Happy to help. 👍️