Display Formula Problem

  • Thread starter Thread starter Ray Moon
  • Start date Start date
R

Ray Moon

All,

I have search Microsoft's Knowledge Base, but could not find an answer.
Hopefully, someone here can solve my problem.

I am using Excel 2002. I have several project managers maintaining budgets
in Excel workbooks. I am creating a summary Excel workbook. To get the data
out of the workbooks, I start with entering an equal sign, click on the cell
in one of the other workbooks, and press enter. The value from the source
workbook is displayed correctly.

Since I want to capture several columns and rows around this first cell, I
want to copy formula in the first cell into near cells, I must remove the
absolute cell reference in the first cell. When I remove the dollar signs,
either using the F4 key or just deleting the dollar signs, the formula now
is displayed vice the value that the formula represents. All changes I tried
for the cell format in the Number tab in Format Cells dialog box did not
change the display, such as text or general. When I checked the View tab of
the Options, the Formulas check box was not selected. Checking this check
box, saving, then clearing this check box and saving did not have any
effect.

Also, very curious is the fact that if I clear the cell, and recreate the
entry the same way as above, the formula is displayed vice the value from
the source workbook. I have to select the entire row and select Edit|Delete
from the main menu, before recreating the entry will display the value vice
the formula.

I have found a work around. The display is not changed if I perform a Search
& Replace of a dollar sign with nothing.

What is causing the cell display problem and is there a better fix or work
around?

Thanks in advance,
Ray
 
I will guess that the formula is pointing to a cell that contains text,
and a text format is getting set on the cell. When you edit the
formula, that text format causes the the cell contents to be interpreted
as a text string that happens to start with "=" instead of a formula.
Reformatting a cell does not change the cell contents; in particular it
will not change something from text to a formula. Once you remove the
text format, you will need to re-enter the formula before that change
will take place.

Jerry
 
Jerry,

Thank you for the response.

Originally, there was not any format for the cell. I changed it to General,
with no effect, then to Text with no effect, and back to General with no
effect. When I re-enter the formula, the address is absolute which starts
the process all over again!

Ray
 
Changing the cell format does not change the contents of the cell, only
how they are displayed.

If no format is on the cell, then the cell format is General, yet you
say you that you changed it to General (from what?).

Before entering the formula, you have the choice of whether to enter it
as an absolute or a relative cell reference.

My guess has not changed materially. I suspect that
(1) you start with an unformatted (General format) cell.
(2) you press "=" and then use a mouse to click on a cell in another
workbook, resulting in an absolute cell reference
(3) you press Enter to keep the formula, at which time you get the
result that you expect
(4) you then edit the formula to remove the "$" characters so that you
can copy the formula to other cells
(5) when you press Enter to keep that modified formula, instead of
getting the result that you expect, the cell now displays the equation
(6) when you examine the format of the cell, it now shows as Text, so
you change it to General with no effect

If this sequence of events correctly describes your problem, then my
original guess and answer were correct. I will elaborate, since my
first reply apparently failed to communicate:

Just changing the format in step (6) does not solve the problem, since
if the value in the cell is now a text string (the only way I can think
of for it to now display a formula without being configured to), then
changing the cell format will not cause it to stop being a text string,
it merely prepares the cell to receive something other than text. After
changing the format to general, click your cursor in the middle of the
formula and press enter to re-enter the formula; it should now behave as
expected.

Alternately, you could reverse steps 3 and 4, so that you do all of your
formula editing before you originally commit the formula.

Jerry
 
And just to add to what Jerry wrote.

This automatic conversion from General to Text is excel's way of helping you!

Try this on a test worksheet:

put the date in A1 (just hit ctrl-semicolon)
put =A1 in cell B1. Excel helped you by copying that date format to the formula
cell.

The same thing happens when you format A2 as Text and put =A2 (in B2).

As soon as you hit enter, B2 inherited the Text format of A2. Now when you
editted B2, excel saw the cell as text and just kept it as text--so now you
could see the formula!

If you corrected the formula before the first enter, you'd be ok.

When I do this, I fix my formula, change the format to general (but it's still
just text), then I edit|replace = with = (to force xl to see it as a formula
again).

Just a word of warning, though. Remember what you did. You'll be doing it
again (and again and again).
 
Back
Top