| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Formula not working
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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 "Mike H" wrote: 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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
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) |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
e-mail address is OK so don't see why you have a problem.
Post yours "holyman" wrote: 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) |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula not working
File received
"holyman" wrote: 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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula not working | Excel Worksheet Functions | |||
| Formula Sometimes Working Sometimes Not | Excel Discussion (Misc queries) | |||
| Formula not working | Excel Worksheet Functions | |||
| Formula is not working | Excel Discussion (Misc queries) | |||
| Why isn't this formula working? | Excel Discussion (Misc queries) | |||
Linear Mode
