 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / August 2007IF 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) 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 > On Sheet 1 have the following data > Column A Column B Column C Column D [quoted text clipped - 11 lines] > Row 2 should return Gateshead (CAR) > Row 3, 4 and 5 should return Newport (Comm) 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 > Hi, > [quoted text clipped - 23 lines] > > Row 2 should return Gateshead (CAR) > > Row 3, 4 and 5 should return Newport (Comm) try: =INDEX(Sheet1!A2:D2,MAX((Sheet1!A2:D2<>"")*COLUMN(Sheet1!A2:D2))) Enter with Control + shift+enter Copy down > On Sheet 1 have the following data > Column A Column B Column C Column D [quoted text clipped - 11 lines] > Row 2 should return Gateshead (CAR) > Row 3, 4 and 5 should return Newport (Comm) 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 > try: > [quoted text clipped - 19 lines] > > Row 2 should return Gateshead (CAR) > > Row 3, 4 and 5 should return Newport (Comm) 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 > Sorry, but that did not work either - returns #REF! message > [quoted text clipped - 26 lines] > > > Row 2 should return Gateshead (CAR) > > > Row 3, 4 and 5 should return Newport (Comm) 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' formula works perfectly so if you're getting a #REF error your not > copying it correctly or perhaps you don't have a sheet1 [quoted text clipped - 31 lines] > > > > Row 2 should return Gateshead (CAR) > > > > Row 3, 4 and 5 should return Newport (Comm) 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. > 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 [quoted text clipped - 39 lines] > > > > > Row 2 should return Gateshead (CAR) > > > > > Row 3, 4 and 5 should return Newport (Comm) 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 > The test has be be <> otherwise it won't work! > [quoted text clipped - 49 lines] > > > > > > Row 2 should return Gateshead (CAR) > > > > > > Row 3, 4 and 5 should return Newport (Comm) 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 > I can't understand why its not working, have spent the last hour trying to > fathom out with no success [quoted text clipped - 60 lines] > > > > > > > Row 2 should return Gateshead (CAR) > > > > > > > Row 3, 4 and 5 should return Newport (Comm) tried to email it but won't go through - have taken out the no spam > Are you entering it with Ctrl+Shift+Enter? > [quoted text clipped - 70 lines] > > > > > > > > Row 2 should return Gateshead (CAR) > > > > > > > > Row 3, 4 and 5 should return Newport (Comm) e-mail address is OK so don't see why you have a problem. Post yours > tried to email it but won't go through - have taken out the no spam > > Are you entering it with Ctrl+Shift+Enter? > > [quoted text clipped - 70 lines] > > > > > > > > > Row 2 should return Gateshead (CAR) > > > > > > > > > Row 3, 4 and 5 should return Newport (Comm) File received > tried to email it but won't go through - have taken out the no spam > [quoted text clipped - 72 lines] > > > > > > > > > Row 2 should return Gateshead (CAR) > > > > > > > > > Row 3, 4 and 5 should return Newport (Comm) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.