vlookups - formating

[Guest]

i have spreadsheet using various vlookups from other sheets. Need to special
paste values of vlookups in new sheet. If vlookup picks up a blank cell then
the pasted value on new sheet is showing ' instead of a blank cell. I need
to have a blank cell and not a label symbol. How do I and on what sheet do I
change format and which format should I be using? sheet 1 is vlookup
formulas, sheet 2 and 3 is information sheet 1 is looking up. I need the new
sheet to save as txt file for importing to other program.

 

[Peo Sjoblom]

Never heard of that, if vlookup picks up a blank cell a zero is returned or
if it can't find a match #N/A is returned. Post your formula that returns an
apostrophe


--


Regards,


Peo Sjoblom

 

[Guest]

Not if I have an "if" statement as well. The vlookup is not the problem.
The problem comes when trying to special paste values into another sheet. I
need a blank cell and not an ' showing in the new sheet.

 

[Peo Sjoblom]

Post the formula you are using that returns an apostrophe when copied and
pasted as value


--


Regards,


Peo Sjoblom

 

[Guest]

It finds the match. The match just has no information in it cell it is
looking at is blank- which in some cases is correct. That part is working
correctly. Problem is when pasting results into new worksheet.

 

[Dave Peterson]

You can only see that apostrophe if you have a setting toggled on.

In xl2003, it's under:
tools|Options|transition tab|Transition Navigation keys

But if you turn this setting off, the cell still won't be empty--but the
apostrophe will be invisible.

If I'm converting to values, I'll do this:

Select the range to fix
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

========
If I'm building the formula from scratch, then instead of using:
=if(isna(vlookup(...)),"",vlookup(...))

I'll just use:
=vlookup(...)

And then I'd convert to values and replace #N/A with nothing. It makes the
formula simpler and I have fewer Edit|Replaces to do.

 

[Pete_UK]

I've come across it many times before, Suesea, when you have a formula
like:

=IF(ISNA(vlookup(...)),"",vlookup(...))

and so you have a formula-blank in the cell if there is no match from
the vlookup. When you fix these values you end up with ' in the cell
(as it wasn't completely blank).

I don't know how to get rid of it easily.

Pete

 

[Dave Peterson]

ps. I do the edit|replaces after converting to values.

 

[Guest]

Thanks Dave: It was the toggle!