Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
I currently have the following code.....
=IF(T8<>"";VLOOKUP(L8;doggy.csv!$A$1:$B$2000;2;0);"")
If nothing is there in my reference file.....how can I stop the VLOOKUP providing a #N/A into the cell.
Also...If a value already exists in the cell....How can I stop it from being overwriten with #N/A or whatever Ive substituted for #N/A ie so it first looks to see if anything is in the cell and if there is it leaves it alone. If there is not anything...it checks the vlookup and puts that data in or if there is nothing it puts nothing as opposed to #N/A
Is this a tricky one or can it be easily accomplished.
Thanks
Simon
| Check out our Excel Resources | ||||
 |
 |
|||
Aladin
You could use the ISBLANK and ISERROR functions.
=IF(ISBLANK(A1),IF(ISERROR(YourVLOOKUP),"",Your Vlookup),A1)
I am now using the example.....
=IF(ISBLANK(T8);"";IF(ISNUMBER(MATCH(L8;doggy.csv!$A$1:$A$2000;0));VLOOKUP(L8;doggy.csv!$A$1:$B$2000;2;0);""))
This seems to solve my #N/A problem but it does not solve the problem if there is something already in the cell I want it to stay and not be overwritten. This current formular will overwrite anything that is already in the cell. Is it possible to first test if there is anything in the cell.....and only if the cell is empty should the above forular be brought into play.
Thanks for your help on this.
Simon
=IF(T8<>"";if(isna(VLOOKUP(L8;doggy.csv!$A$1:$B$2000;2;0));"";VLOOKUP(L8;doggy.csv!$A$1:$B$2000;2;0);""))
The above will help you get around the N/A problem.
In the next part, I do not fully understand why it is going to override an existing cell, Are you copying a formula down the whole column?
If this is the case you will need to write a macro to test each cell before pasting the value(formula) in
waiting for some more input...
It is not possible for a formula to check its own cell in order to see whether there is already a value there and act on the result of that test.
You can of course use some intrusive VB code to do what you want.
Aladin
Thanks for your help so far.
How would I add this code to a Macro.
What would the code look like.
Do I need to have a specialist VB programer or is this something I could do myself. (Im not a programer)
Any assistance would be appreciated.
Thanks
Simon
Yes I am pasting down the whole column. I think I need some vb script to deal with this problem of overwriting data if it already appears in the cell. Do you have any Ideas.
Thanks
Simon