Excel IF Formula Question

[arviy2k]

Ok what am I doing wrong here.

I have a worksheet that lists employee numbers and their cost centers. Some of the cost centers are blanks in the worksheet, but all the employee numbers are always filled.

Basically, I'm trying to fill the blanks in column C (Cost Center), based on the data in Column B(Employee Number).

if the employee number matches the employee number in the cell above the formula, it should take the cost center that is above the formula, and likewise for the cell below where the formula is entered.

What I'm asking excel to do in cell D5 is,
1 - If C4 is blank, and B5=B6, Pick the cost center in C6.
2 - If C4 is not blank, and B5=B4, Pick the cost center in C4.

This is the formula I have in Cell D5 - =IF(ISBLANK(C4),IF(B5=B6,C6,0),IF(B5=B4,C4,IF(B5=B6,C6,0)))
To make this formula dynamically update with column C, I entered this formula in C5 - =IF(D5=0,"",D5)

When I copy this formula down, it throws up a circular reference error.

Can someone please help me out as to why this formula is failing, or if an alternative formula can be used in its place?

Below is a screenshot of the file. I am using Excel 2010 on Windows 7. Thank you so much for your help!

Regards,
Aravind

 

[bisho]

=if(and(isblank(c4),b5=b6),c6,if(and(c4<>"",b5=b4),c4))

 

[arviy2k]

=if(and(isblank(c4),b5=b6),c6,if(and(c4<>"",b5=b4),c4))

Thanks Bisho!

This formula helped a lot. Though I still need to do a bit of tweaking to make it perfect, this is a good start.

Thanks again!
Aravind