IF Formula not working

[holyman]

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)

 

[Mike H]

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="",Sheet1!$B$2,IF(Sheet1!$B$2="",Sheet1!$A$2)))

Note that the space between the quotes has gone.

Mike

 

[Toppers]

try:

=INDEX(Sheet1!A22,MAX((Sheet1!A22<>"")*COLUMN(Sheet1!A22)))

Enter with Control + shift+enter

Copy down

 

[holyman]

Thanks Mike for quick response however still returning 'false'.

Need it to return the text in Column D. If no text in Column D, then return
column C, if column C is blank, return text in column B, and if column B is
blank return text in Column A.
At least one column will have data

Hope this helps. Please help

 

[holyman]

Sorry, but that did not work either - returns #REF! message

Need it to return the text in Column D. If no text in Column D, then return
column C, if column C is blank, return text in column B, and if column B is
blank return text in Column A.
At least one column will have data

 

[Mike H]

Toppers' formula works perfectly so if you're getting a #REF error your not
copying it correctly or perhaps you don't have a sheet1

Mike

 

[holyman]

Had to change the <> to a = to make it work. However if their is not text in
Column A, its not returning the text thats in Column B or Column C or Column
D. Its just returning 0 if data is blank in column A

Formula below.........

=INDEX(dwpquery.xls!$I$2:$L$2,MAX((dwpquery.xls!$I$2:$L$2="")*COLUMN(dwpquery.xls!$I$2:$L$2)))

 

[Toppers]

The test has be be <> otherwise it won't work!

The logic finds the Maximum column which is non-blank.

and the cell references should be :

dwpquery.xls!$I2:$L2 not dwpquery.xls!$I$2:$L$2

if you are copying the formula down rows.

 

[holyman]

I can't understand why its not working, have spent the last hour trying to
fathom out with no success

Data is on sheet 'dwpquery.xls' with data being in columns I to L.
=INDEX(dwpquery.xls!$I3:$L3,MAX((dwpquery.xls!$I3:$L3<>"
")*COLUMN(dwpquery.xls!$I3:$L3)))

What am I doing wrong............is there another formula I can use

Many thanks

 

[Toppers]

Are you entering it with Ctrl+Shift+Enter?

You will get {} brackets round the formula if this is done correctly.

If you still have problems, post w/book to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

 

[holyman]

tried to email it but won't go through - have taken out the no spam

 

[Toppers]

e-mail address is OK so don't see why you have a problem.

Post yours

 

[Toppers]

File received

tried to email it but won't go through - have taken out the no spam