Formatting cells to display formula

[Tom_R._in_OK]

I've got a situation where I need to enter short formulas into a range of cells (e.g. "=66.67% * 1/24"), and when I print the worksheet, I need those formulas to display as entered rather than as an evaluated number in decimal format.

Is there a way to do this, without turning on show formulas for the whole workbook in preferences? I've tried entering the formulas with a " ' " in front so it still displays correctly, but other cells can't reference the value as a number.

Any help appreciated.

Thanks,

Tom

 

[Phillip Jones, CET]

Can't actually help with your problem.
I can explain why the cell with the "'" before the formula no longer works.

by adding the "'" you made the contents of the cell a text object no a
number or formula.

I've got a situation where I need to enter short formulas into a range
of cells (e.g. "=66.67% * 1/24"), and when I print the worksheet, I need
those formulas to display as entered rather than as an evaluated number
in decimal format.

Is there a way to do this, without turning on show formulas for the
whole workbook in preferences? I've tried entering the formulas with a "
' " in front so it still displays correctly, but other cells can't
reference the value as a number.

Any help appreciated.

Thanks,

Tom

--
------------------------------------------------------------------------
Phillip M. Jones, CET |MEMBER:VPEA (LIFE) ETA-I, NESDA,ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

 

[JE McGimpsey]

I've got a situation where I need to enter short formulas into a range of
cells (e.g. "=66.67% * 1/24"), and when I print the worksheet, I need those
formulas to display as entered rather than as an evaluated number in decimal
format. <br><br>Is there a way to do this, without turning on show formulas
for the whole workbook in preferences? I've tried entering the formulas with
a " ' " in front so it still displays correctly, but other cells can't
reference the value as a number. <br><br>Any help appreciated.
<br><br>Thanks, <br><br>Tom

Type CTRL-` (accent grave, on the key above the Tab key on a standard US
keyboard) to display formulas.

Technically, this is just a keyboard shortcut for "turning on show
formulas for the whole workbook in preferences", so I don't know whether
this will meet your needs. If not, post back with more detail on what
you're actually trying to do.

 

[CyberTaz]

AFAIK there is no way to have some cells display/print formula results while
others display the formulas & retain the operation of those that are
displayed - it's an all or nothing proposition.

 

[Tom_R._in_OK]

If it is an all or nothing proposition, what about the possibility of entering it as text, and then using a function to evaluate the text as a formula? Is there a function in Excel that can evaluate text as a formula?

Thanks,

Tom

 

[Bob Greenblatt]

If it is an all or nothing proposition, what about the possibility of entering
it as text, and then using a function to evaluate the text as a formula? Is
there a function in Excel that can evaluate text as a formula?

Thanks,

Tom

I'm not sure what you are trying to do. You can toggle between the display
of formulas and values by control-back apostrophe 9the key to the left of 1.
You can diagnose and debug formulas by highlighting a portion of the formula
(make sure you have correctly selected matching parens) and press F9. If you
are trying to do something else, please describe it in more detail so we can
help.

 

[Tom_R._in_OK]

please describe it in more detail so we can

help.

Sure. I use a spreadsheet with a number of calculations that all reference a particular cell, for example A1. Cell A2 might be "=A1*640" or some other value. A3 may equal some value times A1; other cells may reference A2 or A3.

The info I enter in A1 is usually a short formula, such as 33%*3/16 or 1/2*(1/8 - 1/16). For my purposes, looking at that formula is much more helpful than seeing the decimal equivalent. What I want to do is format A1 to print and display the formula (33%*3/16) or whatever, but still have all other cells (A2, A3, etc) display their result rather than their formula.

So toggling between displaying all formulas is not a solution.

Is there a way to do this? It was simple to do in Quattro Pro.

Thanks for any help.

 

[Bob Greenblatt]

Sure. I use a spreadsheet with a number of calculations that all reference a
particular cell, for example A1. Cell A2 might be "=A1*640" or some other
value. A3 may equal some value times A1; other cells may reference A2 or A3.

The info I enter in A1 is usually a short formula, such as 33%*3/16 or
1/2*(1/8 - 1/16). For my purposes, looking at that formula is much more
helpful than seeing the decimal equivalent. What I want to do is format A1 to
print and display the formula (33%*3/16) or whatever, but still have all other
cells (A2, A3, etc) display their result rather than their formula.

So toggling between displaying all formulas is not a solution.

Is there a way to do this? It was simple to do in Quattro Pro.

Thanks for any help.

OK, you can't see the formula text in the cell whose formula is being
evaluated, but you certainly can see it in another cell. This is a little
tricky but will work fine in all versions of Excel. Assuming you enter the
formula whose text you wish to show in A1:
Go to Insert-Name-Define. Pick a name like "FormulaText" and enter this in
the names in workbook box. In the refers to section, enter
"=Get.cell(6,$a$1)", no quotes of course. Now in the cell where you wish to
see A1's formula's text enter the formula =formulatext

Note: if you want to see other cells' formula text also, then do not enter
the reference to A1 as absolute. Then any cell displaced from the cell where
you defined the name containing the exact same formula (=formulatext) will
display the formula text of the offset cell. So, if E6 was active when you
defined the name referring to A1, then placing =formulatext in E7 will
display the formula from A2, and in F7, the text of B2, etc.

I hope this helps

 

[JE McGimpsey]

OK, you can't see the formula text in the cell whose formula is being
evaluated, but you certainly can see it in another cell. This is a little
tricky but will work fine in all versions of Excel. Assuming you enter the
formula whose text you wish to show in A1:
Go to Insert-Name-Define. Pick a name like "FormulaText" and enter this in
the names in workbook box. In the refers to section, enter
"=Get.cell(6,$a$1)", no quotes of course. Now in the cell where you wish to
see A1's formula's text enter the formula =formulatext

Note: if you want to see other cells' formula text also, then do not enter
the reference to A1 as absolute. Then any cell displaced from the cell where
you defined the name containing the exact same formula (=formulatext) will
display the formula text of the offset cell. So, if E6 was active when you
defined the name referring to A1, then placing =formulatext in E7 will
display the formula from A2, and in F7, the text of B2, etc.

One minor caution: Copying a worksheet that contains a reference to an
XL4M command (like Get.Cell) to another workbook will cause all versions
of MacXL (and some earlier versions of WinXL) to crash.

 

[Tom_R._in_OK]

Bob, thanks for the tip, and J.E., thanks for the warning.

I can make this work if I enter the absolute cell reference ("$a$1"), but if i just enter it as ("a1"), then when I enter =formulatext in the cell where I want the formula to display, it displays "=formulatext" rather than the formula itself.

What I entered as the reference for insert-name-define formulatext was "Get.cell(6,d9)" (without the quotation marks). Entering Get.cell(6,$d$9) works, but as you said, displays info for the single absolute cell only.

Thanks,

Tom

 

[Bob Greenblatt]

Bob, thanks for the tip, and J.E., thanks for the warning.

I can make this work if I enter the absolute cell reference ("$a$1"), but if i
just enter it as ("a1"), then when I enter =formulatext in the cell where I
want the formula to display, it displays "=formulatext" rather than the
formula itself.

What I entered as the reference for insert-name-define formulatext was
"Get.cell(6,d9)" (without the quotation marks). Entering Get.cell(6,$d$9)
works, but as you said, displays info for the single absolute cell only.

Thanks,

Tom

The refers to entry needs to have an equal sign =get.cell(6,$d$9). Make
sure you have the correct cell active when you define the name. If youu use
a relative reference in the definition, it will be relative to the Active
cell, not the cell in the formula.

 

[Tom_R._in_OK]

I mis-"spoke" in my earlier post. The refers-to entry does contain "=get.cell(6,d9)". If I only put "=get.cell(6,d9)", the reference doesn't work and the cell with =formulatext only displays "=formulatext".

If I change it to "=get.cell(6,$d$9)", then the =formulatext reference works and I see the formula in d9.

Am I still missing something?

Thanks for all the help,

Tom

 

[Bob Greenblatt]

I mis-"spoke" in my earlier post. The refers-to entry does contain
"=get.cell(6,d9)". If I only put "=get.cell(6,d9)", the reference doesn't work
and the cell with =formulatext only displays "=formulatext".

If I change it to "=get.cell(6,$d$9)", then the =formulatext reference works
and I see the formula in d9.

Am I still missing something?

Thanks for all the help,

Tom

I think so. What is the active cell when you enter the refers to in the
define name? It works fine for me whether or not I use absolute or relative
references. What may help you to figure out what is happening is to select
another cell, then look in Insert-Name-Define, click on the name and look at
the refers to of the defined name to see what it is referencing. The reason
it is displaying =formulatext in some cells is because that is indeed the
formula text of the referenced cell. This relative reference stuff is pretty
tricky, so look at the refers to one cell at a time.