Display an Excel formula in an adjacent cell
Hello,
In Excel (2007), I know that an entire worksheet can be set to display the values in its cells (default) or the formulas in its cells which produce those values. However, is there a function or some other means whereby individual cells can be set to display the formula in their own or some adjacent cell?
For example, Fig. 1 shows manual entries in A1:A4 (blue) and has the following formula in
B4: =A1&", "&A2&" "&A3&", "&A4&"!"
which results in the sentence shown in B4 (black).
Is there a way to enter or do something in B5 so that it will display the formula in B4 (red) as shown in Fig. 2?
The two possibilities I can think of are to:
1) copy the formula for B4 from the formula bar and paste it into B5 but somehow format B5 so that it displays its formula but leave B4 showing its result (the sentence).
2) enter some function (SomeFxn) into B5 which displays the formula in B4:
B5: =SomeFxn(B4)
Does either of those two exist or is there a way to create one or both?
Thanks
In Excel (2007), I know that an entire worksheet can be set to display the values in its cells (default) or the formulas in its cells which produce those values. However, is there a function or some other means whereby individual cells can be set to display the formula in their own or some adjacent cell?
For example, Fig. 1 shows manual entries in A1:A4 (blue) and has the following formula in
B4: =A1&", "&A2&" "&A3&", "&A4&"!"
which results in the sentence shown in B4 (black).
Is there a way to enter or do something in B5 so that it will display the formula in B4 (red) as shown in Fig. 2? The two possibilities I can think of are to:1) copy the formula for B4 from the formula bar and paste it into B5 but somehow format B5 so that it displays its formula but leave B4 showing its result (the sentence).
2) enter some function (SomeFxn) into B5 which displays the formula in B4:
B5: =SomeFxn(B4)
Does either of those two exist or is there a way to create one or both?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can do that with a VBA function. An example can be found at:
http://www.mvps.org/dmcritchie/excel/formula.htm
http://www.mvps.org/dmcritchie/excel/formula.htm
ASKER
Thanks for the responses.
So I'm assuming that outside of VBA, this capability does not exisst, right?
So I'm assuming that outside of VBA, this capability does not exisst, right?
Correct - You'd have to use VBA to do this.
Alan.
Alan.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Imnorle's solution is cool. It remind me the Excel4 days.
You can also try this variation:
=GET.CELL(6,OFFSET(INDIREC
This will give the formula of the cell immediately above. You can copy or move this to any cell and use it without modification.
You can also try this variation:
=GET.CELL(6,OFFSET(INDIREC
This will give the formula of the cell immediately above. You can copy or move this to any cell and use it without modification.
Of course in order to use an Excel4 function, you need to define it in name, and use the name in the worksheet like Imnorle illustrated.
Yes - I had almost forgotten about the Excel4 macro sheets!
Not recommended going forwards though as I think MS have indicated that support for Excel4 stuff is going away.
Does anyone reading this have Excel 2010 and can confirm if the old Excel4 macro commands can still be used in that edition?
Alan.
Not recommended going forwards though as I think MS have indicated that support for Excel4 stuff is going away.
Does anyone reading this have Excel 2010 and can confirm if the old Excel4 macro commands can still be used in that edition?
Alan.
Alan,
I can confirm that the Excel 4 macro trick still works in Excel 2010. You will get a macro warning, however, when the workbook opens. You will also have to save the workbook in .xlsm file format.
Brad
I can confirm that the Excel 4 macro trick still works in Excel 2010. You will get a macro warning, however, when the workbook opens. You will also have to save the workbook in .xlsm file format.
Brad
Okay - Thanx.
Do you (or anyone else here) recall MS saying that they are phasing out support for Excel4 macro sheets / commands?
Perhaps I imagined it?
Alan.
Do you (or anyone else here) recall MS saying that they are phasing out support for Excel4 macro sheets / commands?
Perhaps I imagined it?
Alan.
Alan
I've never heard anything about it.
Perhaps they've just forgotten about it.
Mind you 'support' could mean couple of things I suppose, one being actually removing the functionality. another not
really acknowledging it's there.
I've never heard anything about it.
Perhaps they've just forgotten about it.
Mind you 'support' could mean couple of things I suppose, one being actually removing the functionality. another not
really acknowledging it's there.
Yes - they surely haven't added any functionality for many years / versions.
Also, they seem to be pushing migration by making sure there are no 'excuses', and I imagine they would prefer to lose excel4 macro compatibility.
I'd say this is strongly indicative that they are looking to remove it from the next version or so of Excel:
http://blogs.msdn.com/b/excel/archive/2010/02/16/migrating-excel-4-macros-to-vba.aspx
Alan.
Also, they seem to be pushing migration by making sure there are no 'excuses', and I imagine they would prefer to lose excel4 macro compatibility.
I'd say this is strongly indicative that they are looking to remove it from the next version or so of Excel:
http://blogs.msdn.com/b/excel/archive/2010/02/16/migrating-excel-4-macros-to-vba.aspx
Alan.
Just a thought, but the OFFSET is unnecessary:
=GET.CELL(6,OFFSET(INDIREC
can just be:
=GET.CELL(6,INDIRECT("R[-1
It's more reliable to use INDIRECT than the !B4 notation though, which doesn't always update properly.
=GET.CELL(6,OFFSET(INDIREC
can just be:
=GET.CELL(6,INDIRECT("R[-1
It's more reliable to use INDIRECT than the !B4 notation though, which doesn't always update properly.
ASKER
Great responses.
Alan.