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

"Toppers" wrote:

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" wrote:

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" wrote:

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" wrote:

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)))

"Mike H" wrote:

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" wrote:

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

"Toppers" wrote:

try:

=INDEX(Sheet1!A2:D2,MAX((Sheet1!A2:D2<"")*COLUMN( Sheet1!A2:D2)))

Enter with Control + shift+enter

Copy down

"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)