Good Evening,
I need to see the formula for cell E5 on Sheet1, but have it displayed in G10 on Sheet 2.
Not the results of the formulas, just the formula itself.
I tried to use the Show formulas in cells instead of their calculated results option, but it only shows the formula in G10 (shows the link to E5), not the original formula in E5
Thanks !
I usually create the following VBA function. Then I would use =myFormula(Sheet!E5).Originally Posted by dgiardina
Function myFormula(r As Range) As String
myFormula = r(1).Formula
End Function
Alternatively, you might consider copying Sheet1, then pressing ctrl-` (accent grave) in Sheet1(1), which you can rename as Sheet2.
The problem with that is: now Excel is doing twice as much work, since Sheet1(1) will be recalculated even though it is in "show formula" mode, I believe.
That might be okay if Sheet1 is not very complicated. Otherwise, it might be prohibitive.
Last edited by joeu2004; 01-11-2015 at 09:29 PM. Reason: cosmetic
I have 100+ tabs in a worksheet that I need to see the formula in E5 for each tab to see what I need to modify. I have a list of my sheets & using the Indirect function was able to list the results for E5, but not the original formulas
This seems like the same as in your other thread...
http://www.excelforum.com/excel-gene...eport-tab.html
If these are not the same, please let me knpow, otherwise I will close this 1 and toy can continue of the other 1
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I do apologize. I was playing around with it over the weekend & got to the point where I can have it pull the result from J10 for each tab onto a summary tab, but can't figure out how to get the original formulas from the original tabs to show through.
I didn't close the other post because I thought my steps to get where I am now may have been wrong.
A
B
1
sheet1
{=LINEST({10,20},{1,2})}
2
sheet2 =G5
3
sheet3 =H5
The formula in B1 is =myformula(INDIRECT(A1&"!E5")). It returns the formula in Sheet1!E5.
Copy B1 into B2:B3 to display the formulas in Sheet2!E5 and Sheet3!E5.
Errata.... Use the following implementation of the VBA function.
Application.Volatile is needed if you intend to use INDIRECT, OFFSET or another function to derive the cell reference (parameter).
Please Login or Register to view this content.
That also handles array-entered formulas correctly. Sadly, myformula = r(1).FormulaArray is not sufficient.
Thanks to joeu2004 for the code !
I kept getting a Value error, so I modified the formula to be: =myformula(INDIRECT("'" & A1 &"'!E5"))
Works perfectly !!
Yes, the single-quotes are needed when the sheet name in A1 contains a space; but it never hurts to use them. Glad you were able to remedy my oversight.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks