Excel 2007 : Autofill Excluding Multiple Blank Cells

[Ichigo]

Hi Friends,

I've been trying for hours but can't get my template right. I'm trying to do an Autofill which will be based on multiple blank cells.

The intention is - If any of the rows in column B & D is blank, the cell should display nothing. But if any of the rows in column B & D has data, i want the autofill to appear as normal.

As attached, the cell in red shows the no. 6 repeated the data above if there's data in column D, but seems OK if there's data in column B. I feel i'm missing something in my formula, but unable to find out what.

Thanks for the help.

[DonkeyOte]

Perhaps you might try:

A3: =IF(B3&D3="","",1+MAX($A$2:$A2))
copied down

[Ichigo]

Thank you friend. It's working as i how i want it to be. May I know what does the MAX is used for and why we choose to lock cell A2? A2 is a text cell.

[DonkeyOte]

May I know what does the MAX is used for...

The MAX is used to determine the last number used in the sequence up to and including the prior cell.

...why we choose to lock cell A2?

Given the above point (ie up to and incl. prior cell) A3 must reference $A$2:$A2 (A4 will reference $A$2:$A3 etc...)

A2 is a text cell

The fact that A2 holds Text is to our advantage as it ensures the MAX returns 0 for $A$2:$A2 which is what you want.

[Ichigo]

Thank you for the explanation. You have been a great help.