Formula referencing other file show formula not result

[robbbo]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a formula in a cell that selects data from another spreadsheet. Both cells are text and often the formula shows in the receiving spreadsheet not the value. I have both cell formated as "text". In older versions of excel, you could never have an "=" sign start in cell without excel always defaulting that as a formula. Now in 2008 it does not work that way any more.

Along the same line I have a formula in a cell that selects data from another spreadsheet. Sometimes the other sheet will have nothing in the cell, just blank. In that case the cell with the formula will display a number "0" rather than just a empty blank cell.

Point is I am trying to have a spreadsheet map data exactly as it is entered in another spreadsheet. It does so with numbers but not text or blank cells. If I 'copy' and 'paste special' "values" it does just that. Why will a formula not do the same???

Any Ideas?

 

[Bob Greenblatt]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel
I have a formula in a cell that selects data from another spreadsheet.
Both cells are text and often the formula shows in the receiving
spreadsheet not the value. I have both cell formated as "text". In older
versions of excel, you could never have an "=" sign start in cell
without excel always defaulting that as a formula. Now in 2008 it does
not work that way any more.

Along the same line I have a formula in a cell that selects data from
another spreadsheet. Sometimes the other sheet will have nothing in the
cell, just blank. In that case the cell with the formula will display a
number "0" rather than just a empty blank cell.

Point is I am trying to have a spreadsheet map data exactly as it is
entered in another spreadsheet. It does so with numbers but not text or
blank cells. If I 'copy' and 'paste special' "values" it does just that.
Why will a formula not do the same???

Any Ideas?

The short answer is: "that's the way it works." It has always worked
that way. As soon as you put an equal sign in a cell, it is interpreted
as a formula (unless that cell is formatted as text, or the equal sign
is preceded by an apostrophe.) If the cell is formatted as text then the
equal sign is considered text, NOT part of a formula.

If the formula refers to a blank cell, the formula will ALWAYS resolve
to the value zero. You can fix this by a custom format, or a formula
that looks something like: =if(len(referenced cell)=0,"",referenced cell)

So, the answer to your last paragraph is "You can't do that. Excel does
not work that way, and it never has.

 

[robbbo]

> The short answer is: "that's the way it works." It has always worked
> that way. As soon as you put an equal sign in a cell, it is interpreted
> as a formula (unless that cell is formatted as text, or the equal sign
> is preceded by an apostrophe.) If the cell is formatted as text then the
> equal sign is considered text, NOT part of a formula.
>
> If the formula refers to a blank cell, the formula will ALWAYS resolve
> to the value zero. You can fix this by a custom format, or a formula
> that looks something like: =if(len(referenced cell)=0,"",referenced cell)
>
> So, the answer to your last paragraph is "You can't do that. Excel does
> not work that way, and it never has.
>
> --
>
> Bobgreenblattatmsndotcom

Thanks for the quick reply. I thought excel had changed as I never was able to get an "=" sign to show up as text before. Do you know of a way to display text without that cell being formated as text? I will try your fix for the blank cells. Thanks again!

 

[Bob Greenblatt]

Thanks for the quick reply. I thought excel had changed as I never was
able to get an "=" sign to show up as text before. Do you know of a way
to display text without that cell being formated as text? I will try
your fix for the blank cells. Thanks again!

Sure, just format it as general. Is the text actually text or is it
numbers you want treated as text? if your formula references another
cell containing text, then the formula will resolve to the text string
regardless of the format of the cell.

 

[robbbo]

> The short answer is: "that's the way it works." It has always worked
> that way. As soon as you put an equal sign in a cell, it is interpreted
> as a formula (unless that cell is formatted as text, or the equal sign
> is preceded by an apostrophe.) If the cell is formatted as text then the
> equal sign is considered text, NOT part of a formula.
>
> If the formula refers to a blank cell, the formula will ALWAYS resolve
> to the value zero. You can fix this by a custom format, or a formula
> that looks something like: =if(len(referenced cell)=0,"",referenced cell)
>
> So, the answer to your last paragraph is "You can't do that. Excel does
> not work that way, and it never has.
>
> --
>
> Bobgreenblattatmsndotcom
>

I tried your =IF(LEN formula and it worked great!!!! Thanks

 

[robbbo]

> Sure, just format it as general. Is the text actually text or is it
> numbers you want treated as text? if your formula references another
> cell containing text, then the formula will resolve to the text string
> regardless of the format of the cell.
>
> --
>
> Bobgreenblattatmsndotcom
>

It is usually just text, but some times it is numbers in a date format MM/DD/YYYY. When it is entered in the other sheet as that format, (MM/DD/YYYY)I want it to return that as a text string.

robbbo

 

[Bob Greenblatt]

It is usually just text, but some times it is numbers in a date format
MM/DD/YYYY. When it is entered in the other sheet as that format,
(MM/DD/YYYY)I want it to return that as a text string.

robbbo

The problem is that if Excel "thinks" it is a date, it will be stored as
a number. You can tell by looking in the formula bar to see if the
contents are different than what the cell is displaying. If it is a
date, then you CAN NOT return it as text. The receiving cell should have
a formula something like:
=if(len(referenced cell)=0,"",text(referenced cell("MM/DD/YYYY"))