If cell is blank then search for Value above.

[kiasor]

Morning all,

I am sure someone here on MrExcel will be able to help with this as the community is so knowledgeable.

I will attach a file image to help explain what i am trying to do.

But basically:

• Column H (Actual finish date has some blanks)
• Column A is order A-Z and if the value in column A is the same then i want to copy the date down into the blanks
• The curve ball is that further down this database there could be 20+ blanks attached to the same order number with a date only on the top order.

I can solve it if there is just one blank underneath - i inserted another column before H and i used the formula =IF(A16=A15,I15,I16) like so:

 

[kiasor]

Another improvement! all the dates that don't need action are now filled.

Thanks so much for your help on this.

But there are now some #CALC! errors. seems to start looking slightly above the cell after a few rows where no action was needed / only a single order?

Note: i can see the RED section is usually looking in the cells adjacent, i noticed it gaps up like this after the above scenario.

Note - If its easier we could use the previous formula and get that to look up and down, then i could combine the columns in a separate equation / function?

 

[Fluff]

You need to adjust the ranges to match your actual data.

 

[kiasor]

I don't think so. i pasted it into row 2 and then filled down and it works fine. except it starts to build this GAP like so after this row?

so in H31 its still fine.

Then because it was a row with a single order and it had a date in (so that particular order was actually one of the few that's fine)...... after the next row it starts to form a gap (RED).

here it is working up until here.

Then next row. i have drawn a red arrow.

 

[kiasor]

Can you see what i mean?

Ahhh i think i see whats gone wrong. The range is fixed $I$2:$I$30 - so after this row it stops working from then onwards.

Could that part be made dynamic?

 

[Fluff]

The range is fixed $I$2:$I$30

That's why I said you need to adjust the ranges to match your data.