Hi,

It's difficult to see how your data are laid out becuase of the way it has
pasted in but are you reaaly looking for a space " " in D2 or an empty
string. Try changing the formula to:-

=IF(Sheet1!$D$2="",Sheet1!$C$2,IF(Sheet1!$C$2="",S heet1!$B$2,IF(Sheet1!$B$2="",Sheet1!$A$2)))

Note that the space between the quotes has gone.

Mike
"holyman" wrote:

On Sheet 1 have the following data
Column A Column B Column C Column D
NEWPORT (COMM) LIVERPOOL GATESHEAD (CAR)
NEWPORT (COMM)
NEWPORT (COMM)
GATESHEAD (CAR) NEWPORT (COMM)
NEWPORT (COMM)

On Sheet 2 have the following formula to return the latest dealer.
=IF(sheet1!$D$2=" ",sheet1!$C$2,IF(sheet1!$C$2="
",sheet1!$B$2,IF(sheet1!$B$2=" ",sheet1!$A$2))). But my formula is not
returning any values. Please help

Row 2 should return Gateshead (CAR)
Row 3, 4 and 5 should return Newport (Comm)