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.
--
suesea

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



"Suesea" wrote in message
...
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.
--
suesea

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.
--
suesea


"Peo Sjoblom" wrote:

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



"Suesea" wrote in message
...
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.
--
suesea

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


--


Regards,


Peo Sjoblom



"Suesea" wrote in message
...
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.
--
suesea


"Peo Sjoblom" wrote:

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



"Suesea" wrote in message
...
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.
--
suesea

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.
--
suesea


"Peo Sjoblom" wrote:

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



"Suesea" wrote in message
...
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.
--
suesea

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.

Suesea wrote:

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.
--
suesea

--

Dave Peterson

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

On Nov 6, 6:02 pm, Suesea wrote:
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.
--
suesea



"Peo Sjoblom" wrote:
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

"Suesea" wrote in message
...
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.
--
suesea- Hide quoted text -

- Show quoted text -

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

Dave Peterson wrote:

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.

Suesea wrote:

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.
--
suesea

--

Dave Peterson

--

Dave Peterson

Thanks Dave: It was the toggle!