Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
Hi:
When I use this formula - IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),,(VLOOKUP( D9,$A$2:$B$15,2,FALSE))) The cell return is 0. IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE))," ",(VLOOKUP(D9,$A$2:$B$15,2,FALSE))) The cell return is BLANK. What I want is for the current cell data to remain, if the "IF<ISNA<VLOOKUP" function is TRUE instead of the notorious #NA, Blank, and "0" returns. What should I do? Please help. Thanks, Jay |
#2
|
|||
|
|||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
Hi
What do you mean with 'What I want is for the current cell data to remain, ....'? You can have in cell either a value or a formula. And the formula returns a value only to cell where it resides. To make it short - whenever you enter a formula into some cell, the previous entry there is lost after first character is typed into! Arvi Laanemets wrote in message oups.com... Hi: When I use this formula - IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),,(VLOOKUP( D9,$A$2:$B$15,2,FALSE))) The cell return is 0. IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE))," ",(VLOOKUP(D9,$A$2:$B$15,2,FALSE))) The cell return is BLANK. What I want is for the current cell data to remain, if the "IF<ISNA<VLOOKUP" function is TRUE instead of the notorious #NA, Blank, and "0" returns. What should I do? Please help. Thanks, Jay |
#3
|
|||
|
|||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
The current cell data is the result of the formula, ant previous value has
been over-written. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Hi: When I use this formula - IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),,(VLOOKUP( D9,$A$2:$B$15,2,FALSE))) The cell return is 0. IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE))," ",(VLOOKUP(D9,$A$2:$B$15,2,FALSE))) The cell return is BLANK. What I want is for the current cell data to remain, if the "IF<ISNA<VLOOKUP" function is TRUE instead of the notorious #NA, Blank, and "0" returns. What should I do? Please help. Thanks, Jay |
#4
|
|||
|
|||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
Ok. Maybe I should clarify.
My requirement is - I have two spreadsheets (SS1 & SS2) with, say, two columns (A & B) each. My source list is SS1-Columns A&B. My target list is SS2-Columns A&B. I'd like to lookup items in target SS2-Col A in source SS1-Col A and if there is a match - REPLACE target SS2-Col B with source SS1- Col B. If there is NO match - Leave target SS2-Col B alone without replacing it with blank, 0 or #NA. I was typing the IF<ISNA<VLOOKUP formula (in previous email) in the target SS2-Col B cell and expecting it to either REPLACE or retain the previous value it had before the formula was typed - bummer. Well, if I can't use this formula to do that, How could I do this? - Thanks |
#5
|
|||
|
|||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
Hi
This is exactly what we both did say. You can have in column SS2!B:B either VLOOKUP formula, or previous value. But not both! Maybe 3rd column will do for you - where the value from sheet SS1 is displayed, when such is present, or the value from column B is displayed, when no matching entry exists in SS1!A:A. Like C2=IF(ISERROR(VLOOKUP(A2,SS1!$A$2:$B$100,2,0)),B2, VLOOKUP(A2,SS1!$A$2:$B$100 ,2,0)) NB! You can't have this formula in column B !!! Arvi Laanemets "Jay" wrote in message oups.com... Ok. Maybe I should clarify. My requirement is - I have two spreadsheets (SS1 & SS2) with, say, two columns (A & B) each. My source list is SS1-Columns A&B. My target list is SS2-Columns A&B. I'd like to lookup items in target SS2-Col A in source SS1-Col A and if there is a match - REPLACE target SS2-Col B with source SS1- Col B. If there is NO match - Leave target SS2-Col B alone without replacing it with blank, 0 or #NA. I was typing the IF<ISNA<VLOOKUP formula (in previous email) in the target SS2-Col B cell and expecting it to either REPLACE or retain the previous value it had before the formula was typed - bummer. Well, if I can't use this formula to do that, How could I do this? - Thanks |
#6
|
|||
|
|||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
Arvi:
Your suggestion to place the formula in SS2:Col 3 worked like a charm except that when I drag the formula down the row, the cells automatically get written with the current value in Col B. The matching value from SS1Col B is written ONLY if I do a search in that spreadsheet to look for SS2ColA value - defeating the whole purpose! Please help! Thanks, Jay |
#7
|
|||
|
|||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
Hi
Post your formula from some cell here - p.e. the one working, and the nex one (not working). Probably your range references are wrong (relative vs. absolute reference) Arvi Laanemets "Jay" wrote in message oups.com... Arvi: Your suggestion to place the formula in SS2:Col 3 worked like a charm except that when I drag the formula down the row, the cells automatically get written with the current value in Col B. The matching value from SS1Col B is written ONLY if I do a search in that spreadsheet to look for SS2ColA value - defeating the whole purpose! Please help! Thanks, Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To leave the cell Blank | Excel Worksheet Functions | |||
How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
To Leave Cell blank Please | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |