This is actually part of a larger project I am working on. My overall
intention is the following:
I have a "Daily Production" sheet which must be filled out each day by a
supervisor (not very computer-literate). To ease and standardize the data
entry, I want to have the supervisor enter a three digit product number,
which designates a specific product we make, and is listed in a separate
Master Product List workbook file. From that three digit number, all the
other descriptive fields (name, size, code date, etc.) would be retrieved
from the Master Product List file. My assumption is that I would use the
LOOKUP function, but I never got that far.

I found that when I type a cell reference to another cell, either on a
different sheet or a different workbook, I only get the formula, not the
actual cell contents. For instance, in cell A1 of sheet 1 I will type
='sheet2'!B12. Instead of the value from that B12 cell, I get the formula I
typed. The same thing happens when I try to access a different workbook, such
as =[Prolst]'sheet2'!B12. Even if I just type "=", then directly point to the
cell I want, I still just get the formula.

The final kicker: I use merged cells on the Daily Production sheet;
therefore the three digit number is coming from a merged cell reference, and
the data coming back from the Master List (which does not have any merged
cells) is being sent to merged cells. If this is a no-no (and I'm strating to
think it is), is there a work-around?

--
Phil Grimes
Plant Engineer
Doumak, Inc.

It sounds like the cells are formatted as TEXT. Format them as GENERAL. Then
to convert the string to a formula:

Select all the cells in question
Goto the menu EditReplace
Find what: =
Replace with: =
Replace all
Close

Merged cells are more trouble than anything else. Avoid using merged cells
at all cost!

Biff

"MM Phil" wrote in message
...
This is actually part of a larger project I am working on. My overall
intention is the following:
I have a "Daily Production" sheet which must be filled out each day by a
supervisor (not very computer-literate). To ease and standardize the data
entry, I want to have the supervisor enter a three digit product number,
which designates a specific product we make, and is listed in a separate
Master Product List workbook file. From that three digit number, all the
other descriptive fields (name, size, code date, etc.) would be retrieved
from the Master Product List file. My assumption is that I would use the
LOOKUP function, but I never got that far.

I found that when I type a cell reference to another cell, either on a
different sheet or a different workbook, I only get the formula, not the
actual cell contents. For instance, in cell A1 of sheet 1 I will type
='sheet2'!B12. Instead of the value from that B12 cell, I get the formula
I
typed. The same thing happens when I try to access a different workbook,
such
as =[Prolst]'sheet2'!B12. Even if I just type "=", then directly point to
the
cell I want, I still just get the formula.

The final kicker: I use merged cells on the Daily Production sheet;
therefore the three digit number is coming from a merged cell reference,
and
the data coming back from the Master List (which does not have any merged
cells) is being sent to merged cells. If this is a no-no (and I'm strating
to
think it is), is there a work-around?

--
Phil Grimes
Plant Engineer
Doumak, Inc.

AArgh!

After further review, it looks like the problem was formatting. the cells I
was writing the formulae in were formatted as "text". Once I switched the
cells to "general", everything accessed the desired cells correctly, even to
a closed workbook. However, I'm still working on that LOOKUP issue; it
probably has at least something to do with formatting as well, since the
three digit number is formatted as "number", and the cells on the Master List
are as "text".
--
Phil Grimes
Plant Engineer
Doumak, Inc.


"MM Phil" wrote:

This is actually part of a larger project I am working on. My overall
intention is the following:
I have a "Daily Production" sheet which must be filled out each day by a
supervisor (not very computer-literate). To ease and standardize the data
entry, I want to have the supervisor enter a three digit product number,
which designates a specific product we make, and is listed in a separate
Master Product List workbook file. From that three digit number, all the
other descriptive fields (name, size, code date, etc.) would be retrieved
from the Master Product List file. My assumption is that I would use the
LOOKUP function, but I never got that far.

I found that when I type a cell reference to another cell, either on a
different sheet or a different workbook, I only get the formula, not the
actual cell contents. For instance, in cell A1 of sheet 1 I will type
='sheet2'!B12. Instead of the value from that B12 cell, I get the formula I
typed. The same thing happens when I try to access a different workbook, such
as =[Prolst]'sheet2'!B12. Even if I just type "=", then directly point to the
cell I want, I still just get the formula.

The final kicker: I use merged cells on the Daily Production sheet;
therefore the three digit number is coming from a merged cell reference, and
the data coming back from the Master List (which does not have any merged
cells) is being sent to merged cells. If this is a no-no (and I'm strating to
think it is), is there a work-around?

--
Phil Grimes
Plant Engineer
Doumak, Inc.

Phil

I do not use merged cells due to the many problems they can cause, but I would
say that merged cells are not the cause of your formulas showing up as text.

The usual reason you see the text of a formula rather than the results is that
the cell was formatted as text prior to inserting the formula.

Format the cell(s) as General then F2 and ENTER to re-enter the formula.

Works now?

Or you have View Formulas enabled.

Hit CTRL + `(backquote above Tab key) to toggle out of View Formula mode.


Gord Dibben MS Excel MVP

On Thu, 1 Feb 2007 13:21:00 -0800, MM Phil
wrote:

This is actually part of a larger project I am working on. My overall
intention is the following:
I have a "Daily Production" sheet which must be filled out each day by a
supervisor (not very computer-literate). To ease and standardize the data
entry, I want to have the supervisor enter a three digit product number,
which designates a specific product we make, and is listed in a separate
Master Product List workbook file. From that three digit number, all the
other descriptive fields (name, size, code date, etc.) would be retrieved
from the Master Product List file. My assumption is that I would use the
LOOKUP function, but I never got that far.

I found that when I type a cell reference to another cell, either on a
different sheet or a different workbook, I only get the formula, not the
actual cell contents. For instance, in cell A1 of sheet 1 I will type
='sheet2'!B12. Instead of the value from that B12 cell, I get the formula I
typed. The same thing happens when I try to access a different workbook, such
as =[Prolst]'sheet2'!B12. Even if I just type "=", then directly point to the
cell I want, I still just get the formula.

The final kicker: I use merged cells on the Daily Production sheet;
therefore the three digit number is coming from a merged cell reference, and
the data coming back from the Master List (which does not have any merged
cells) is being sent to merged cells. If this is a no-no (and I'm strating to
think it is), is there a work-around?