Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement Problem
I am having trouble with Excel not returning a blank in an IF statement. My
spreadsheet has three tabs. The first tab is a list of tracking numbers that need checked (12 digits). The second tab is a list of tracking numbers (16 digits) that are scanned into the spreadsheet in column A as we receive packages. In column B rows 2 through 600 is the formula =left(a2,12) - adjusted for each row. The third tab reconciles the two lists and has the following formula: Column A - =IF('Sheet1'!A2="","",'Sheet1'!A2) Column B - =IF(A2="","",IF(ISNA(MATCH(A2,D:D,0)),"Not Received","Received")) Column D - =IF('Sheet2'!B2="","",'Sheet2'!B2) Column E - =IF(D2="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) Everything works correctly expect Column E returns "Extra Package" for every blank line. If we scan 300 packages, row 301 through row 600 will return "Extra Package". If I check Column A in Sheet2, there is nothing in these rows. If I hit delete on one of the blank rows in Sheet2, it will fix the problem on Sheet3 but it happens again every time we add data. If I highlight all the blank rows and hit delete, it does not fix the problem on Sheet3??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement Problem
My guess would be that Sheet 2 coulmn A is somehow not truly empty.
A space or other non visible character may be there. This would make Sheet 2 column B non blank and cause the issue you describe. Try something like Column E - =IF(trim(D2)="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) -- If this helps, please remember to click yes. "SixBowls" wrote: I am having trouble with Excel not returning a blank in an IF statement. My spreadsheet has three tabs. The first tab is a list of tracking numbers that need checked (12 digits). The second tab is a list of tracking numbers (16 digits) that are scanned into the spreadsheet in column A as we receive packages. In column B rows 2 through 600 is the formula =left(a2,12) - adjusted for each row. The third tab reconciles the two lists and has the following formula: Column A - =IF('Sheet1'!A2="","",'Sheet1'!A2) Column B - =IF(A2="","",IF(ISNA(MATCH(A2,D:D,0)),"Not Received","Received")) Column D - =IF('Sheet2'!B2="","",'Sheet2'!B2) Column E - =IF(D2="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) Everything works correctly expect Column E returns "Extra Package" for every blank line. If we scan 300 packages, row 301 through row 600 will return "Extra Package". If I check Column A in Sheet2, there is nothing in these rows. If I hit delete on one of the blank rows in Sheet2, it will fix the problem on Sheet3 but it happens again every time we add data. If I highlight all the blank rows and hit delete, it does not fix the problem on Sheet3??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement Problem
Trim will work if it is only spaces, CLEAN will remove non printable
characters. So maybe instead of just trim Clean(Trim(D2)) would work even better. -- If this helps, please remember to click yes. "Paul C" wrote: My guess would be that Sheet 2 coulmn A is somehow not truly empty. A space or other non visible character may be there. This would make Sheet 2 column B non blank and cause the issue you describe. Try something like Column E - =IF(trim(D2)="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) -- If this helps, please remember to click yes. "SixBowls" wrote: I am having trouble with Excel not returning a blank in an IF statement. My spreadsheet has three tabs. The first tab is a list of tracking numbers that need checked (12 digits). The second tab is a list of tracking numbers (16 digits) that are scanned into the spreadsheet in column A as we receive packages. In column B rows 2 through 600 is the formula =left(a2,12) - adjusted for each row. The third tab reconciles the two lists and has the following formula: Column A - =IF('Sheet1'!A2="","",'Sheet1'!A2) Column B - =IF(A2="","",IF(ISNA(MATCH(A2,D:D,0)),"Not Received","Received")) Column D - =IF('Sheet2'!B2="","",'Sheet2'!B2) Column E - =IF(D2="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) Everything works correctly expect Column E returns "Extra Package" for every blank line. If we scan 300 packages, row 301 through row 600 will return "Extra Package". If I check Column A in Sheet2, there is nothing in these rows. If I hit delete on one of the blank rows in Sheet2, it will fix the problem on Sheet3 but it happens again every time we add data. If I highlight all the blank rows and hit delete, it does not fix the problem on Sheet3??? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement Problem
"Paul C" wrote:
Trim will work if it is only spaces, CLEAN will remove non printable characters. So maybe instead of just trim Clean(Trim(D2)) would work even better. True. But even CLEAN(TRIM(...)) will not remove the most common source of "blank" characters: the nonbreaking space which often is captured by copying and pasting from web pages. Refer to http://office.microsoft.com/en-us/ex...561311033.aspx . I suspect TRIM(SUBSTITUTE(D2,CHAR(160)," ")) would be more useful, although it does not hurt to wrap CLEAN around all that as well. PS: To be honest, I have not read the OP's problem, so I don't know if anything of is related. I am merely embellishing Paul's response. ----- original message ----- "Paul C" wrote in message ... Trim will work if it is only spaces, CLEAN will remove non printable characters. So maybe instead of just trim Clean(Trim(D2)) would work even better. -- If this helps, please remember to click yes. "Paul C" wrote: My guess would be that Sheet 2 coulmn A is somehow not truly empty. A space or other non visible character may be there. This would make Sheet 2 column B non blank and cause the issue you describe. Try something like Column E - =IF(trim(D2)="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) -- If this helps, please remember to click yes. "SixBowls" wrote: I am having trouble with Excel not returning a blank in an IF statement. My spreadsheet has three tabs. The first tab is a list of tracking numbers that need checked (12 digits). The second tab is a list of tracking numbers (16 digits) that are scanned into the spreadsheet in column A as we receive packages. In column B rows 2 through 600 is the formula =left(a2,12) - adjusted for each row. The third tab reconciles the two lists and has the following formula: Column A - =IF('Sheet1'!A2="","",'Sheet1'!A2) Column B - =IF(A2="","",IF(ISNA(MATCH(A2,D:D,0)),"Not Received","Received")) Column D - =IF('Sheet2'!B2="","",'Sheet2'!B2) Column E - =IF(D2="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) Everything works correctly expect Column E returns "Extra Package" for every blank line. If we scan 300 packages, row 301 through row 600 will return "Extra Package". If I check Column A in Sheet2, there is nothing in these rows. If I hit delete on one of the blank rows in Sheet2, it will fix the problem on Sheet3 but it happens again every time we add data. If I highlight all the blank rows and hit delete, it does not fix the problem on Sheet3??? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement Problem
This worked without clean. Thanks for the help.
"Paul C" wrote: My guess would be that Sheet 2 coulmn A is somehow not truly empty. A space or other non visible character may be there. This would make Sheet 2 column B non blank and cause the issue you describe. Try something like Column E - =IF(trim(D2)="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) -- If this helps, please remember to click yes. "SixBowls" wrote: I am having trouble with Excel not returning a blank in an IF statement. My spreadsheet has three tabs. The first tab is a list of tracking numbers that need checked (12 digits). The second tab is a list of tracking numbers (16 digits) that are scanned into the spreadsheet in column A as we receive packages. In column B rows 2 through 600 is the formula =left(a2,12) - adjusted for each row. The third tab reconciles the two lists and has the following formula: Column A - =IF('Sheet1'!A2="","",'Sheet1'!A2) Column B - =IF(A2="","",IF(ISNA(MATCH(A2,D:D,0)),"Not Received","Received")) Column D - =IF('Sheet2'!B2="","",'Sheet2'!B2) Column E - =IF(D2="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct Package")) Everything works correctly expect Column E returns "Extra Package" for every blank line. If we scan 300 packages, row 301 through row 600 will return "Extra Package". If I check Column A in Sheet2, there is nothing in these rows. If I hit delete on one of the blank rows in Sheet2, it will fix the problem on Sheet3 but it happens again every time we add data. If I highlight all the blank rows and hit delete, it does not fix the problem on Sheet3??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with If Statement | Excel Worksheet Functions | |||
IF Statement problem | Excel Worksheet Functions | |||
IF Statement problem | Excel Worksheet Functions | |||
if statement problem | Excel Worksheet Functions | |||
I have an "IF"Statement problem...please help!! | Excel Worksheet Functions |