view worksheet formulas

Friends,

Is there a way to view only the worksheet formulas inside a worksheet, or 
print them out? I know how to do this for pivot tables and pivot charts, but 
what about a normal worksheet?

Thanks,

Bill Morgan
0
1/11/2005 11:17:03 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
311 Views

Similar Articles

[PageSpeed] 18

Bill

Ctrl+`

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"bill_morgan_3333" <billmorgan3333@discussions.microsoft.com> wrote in 
message news:3475E172-19E9-4199-B974-709FE618FB5E@microsoft.com...
> Friends,
>
> Is there a way to view only the worksheet formulas inside a worksheet, or
> print them out? I know how to do this for pivot tables and pivot charts, 
> but
> what about a normal worksheet?
>
> Thanks,
>
> Bill Morgan 


0
1/11/2005 11:37:07 PM
Bill

See John Walkenbach's site for his ListFormulas macro.

http://j-walk.com/ss/excel/tips/tip37.htm

"Most users have discovered that Excel has an option that lets you display
formulas directly in their cells: Choose Tools Options, click the View tab,
and select the Formulas checkbox. However, Excel doesn't provide a way to
generate a concise list of all formulas in a worksheet. The VBA macro below
inserts a new worksheet, then creates a list of all formulas, cell addresses
and their current values." 

Gord Dibben Excel MVP


On Tue, 11 Jan 2005 15:17:03 -0800, "bill_morgan_3333"
<billmorgan3333@discussions.microsoft.com> wrote:

>Friends,
>
>Is there a way to view only the worksheet formulas inside a worksheet, or 
>print them out? I know how to do this for pivot tables and pivot charts, but 
>what about a normal worksheet?
>
>Thanks,
>
>Bill Morgan

0
Gord
1/11/2005 11:49:15 PM
And for a third route

http://www.cpearson.com/excel/excelM.htm#PrintFormulas

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"bill_morgan_3333" <billmorgan3333@discussions.microsoft.com> wrote in
message news:3475E172-19E9-4199-B974-709FE618FB5E@microsoft.com...
> Friends,
>
> Is there a way to view only the worksheet formulas inside a worksheet, or
> print them out? I know how to do this for pivot tables and pivot charts,
but
> what about a normal worksheet?
>
> Thanks,
>
> Bill Morgan


0
ken.wright (2489)
1/12/2005 12:18:09 AM
Nick,

Hi ...

Ctrl+ brings up the insert row dialog box, but doesn't show sheet formulas. 
Am I missing something? Please advise. Thanks ...

"Nick Hodge" wrote:

> Bill
> 
> Ctrl+`
> 
> -- 
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> 
> 
> "bill_morgan_3333" <billmorgan3333@discussions.microsoft.com> wrote in 
> message news:3475E172-19E9-4199-B974-709FE618FB5E@microsoft.com...
> > Friends,
> >
> > Is there a way to view only the worksheet formulas inside a worksheet, or
> > print them out? I know how to do this for pivot tables and pivot charts, 
> > but
> > what about a normal worksheet?
> >
> > Thanks,
> >
> > Bill Morgan 
> 
> 
> 
0
1/12/2005 2:13:02 AM
Gord,

Thanks for the tip. I'm on my way there, now. 

"Gord Dibben" wrote:

> Bill
> 
> See John Walkenbach's site for his ListFormulas macro.
> 
> http://j-walk.com/ss/excel/tips/tip37.htm
> 
> "Most users have discovered that Excel has an option that lets you display
> formulas directly in their cells: Choose Tools Options, click the View tab,
> and select the Formulas checkbox. However, Excel doesn't provide a way to
> generate a concise list of all formulas in a worksheet. The VBA macro below
> inserts a new worksheet, then creates a list of all formulas, cell addresses
> and their current values." 
> 
> Gord Dibben Excel MVP
> 
> 
> On Tue, 11 Jan 2005 15:17:03 -0800, "bill_morgan_3333"
> <billmorgan3333@discussions.microsoft.com> wrote:
> 
> >Friends,
> >
> >Is there a way to view only the worksheet formulas inside a worksheet, or 
> >print them out? I know how to do this for pivot tables and pivot charts, but 
> >what about a normal worksheet?
> >
> >Thanks,
> >
> >Bill Morgan
> 
> 
0
1/12/2005 2:15:01 AM
Ken,

Thanks for the direction. I'm visiting both the sites mentioned in answers 
to this question.

"Ken Wright" wrote:

> And for a third route
> 
> http://www.cpearson.com/excel/excelM.htm#PrintFormulas
> 
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>               Sys Spec - Win XP Pro /  XL 97/00/02/03
> 
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
> 
> "bill_morgan_3333" <billmorgan3333@discussions.microsoft.com> wrote in
> message news:3475E172-19E9-4199-B974-709FE618FB5E@microsoft.com...
> > Friends,
> >
> > Is there a way to view only the worksheet formulas inside a worksheet, or
> > print them out? I know how to do this for pivot tables and pivot charts,
> but
> > what about a normal worksheet?
> >
> > Thanks,
> >
> > Bill Morgan
> 
> 
> 
0
1/12/2005 2:17:01 AM
Bill

Nick's post was "CRTL + `(backquote above TAB key)


Gord

On Tue, 11 Jan 2005 18:13:02 -0800, "bill_morgan_3333"
<billmorgan3333@discussions.microsoft.com> wrote:

>Nick,
>
>Hi ...
>
>Ctrl+ brings up the insert row dialog box, but doesn't show sheet formulas. 
>Am I missing something? Please advise. Thanks ...
>
>"Nick Hodge" wrote:
>
>> Bill
>> 
>> Ctrl+`
>> 
>> -- 
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>> 
>> 
>> "bill_morgan_3333" <billmorgan3333@discussions.microsoft.com> wrote in 
>> message news:3475E172-19E9-4199-B974-709FE618FB5E@microsoft.com...
>> > Friends,
>> >
>> > Is there a way to view only the worksheet formulas inside a worksheet, or
>> > print them out? I know how to do this for pivot tables and pivot charts, 
>> > but
>> > what about a normal worksheet?
>> >
>> > Thanks,
>> >
>> > Bill Morgan 
>> 
>> 
>> 

0
Gord
1/12/2005 7:21:01 AM
Reply:

Similar Artilces:

Forms & Worksheets
I want to create a form in word that will automatically add entered info into an excel worksheet? Is this possible Charlene, Setup you data in ordered columns, with headings in row 1, then menu Data>Form. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Charlene" <anonymous@discussions.microsoft.com> wrote in message news:A8A03DD4-85DB-4F80-B94A-C568FB95531E@microsoft.com... > I want to create a form in word that will automatically add entered info into an excel worksheet? Is th...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

Compare entries in 2 worksheets and list what does not match
Good Day All; I have 2 Excell works sheets with approx 14000 rows each. What I would like to do is compare both lists and get a 3rd list that shows what entries do not match. Is there a simple way to do ythis in Excel Thanks All Chomp Assuming A1 should equal A1 in the other sheet... =IF(Sheet1!A1=Sheet2!A1,"",Sheet!A1&" does not match "&Sheet2!A1) Auto-Filter for non-blanks. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "The Chomp" wrote: > Good Day All; > > I have 2 ...

a simple math formula
Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

Array Formula #7
I have an array formula that works correct up to the 20th row. It quit running on row 20 or it does not pick up any information after row 20. Any help out ther -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49427 Rather impossible to tell... What's the formula, and what cell(s) is it entered into? What type of data is in any referenced ranges? In article <pete5761.205n5a_1134773401.141...

Excel ask duplicate NAMES when duplicate a worksheets
I have added a NAME called "Above" where point to the cell just above the current cell. The formula is "=INDIRECT("R[-1]C",)" In some workbook, when I duplicate a worksheets, this name will remain silent and work ok. But in some workbooks, when I first duplicate a worksheets, the same name ABOVE will be duplicate and a new local name (belongs to that new worksheet) will be created. If I further duplicate that new worksheets in to a new worksheets, the third worksheets will be warned that a dupicate NAME is existed and ask whether refer to another name or use a ne...

Labels in Formulas requires manual refresh?
I use Labels in Formulas extensively. (Excel 2003 11.5612.5606) That is, I tick off Options/Calculations/"Accept Labels in Formulas" and then use whatever text string I've placed at the top of a column of data to refer to it. It has many benefits over Named Ranges, dynamic or otherwise for my purposes, but one ongoing annoyance. When I add data to the bottom of the column of data, the in-cell formula using the label for reference does not change when it should. Workaround: Select cell, F2 or double click to open it, Enter. I have a number of such formulae so updating can be ...

Scheduling formula question
I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

View as web page in OWA
in OWA 2002, i remember there is a feature "View as web page" on the right side of the email. in OWA 2003, after open the received email, such feature no longer there. Anyway to enable this feature? ...

Somehow I created a Macro in a worksheet.
I created a macro in an Excel worksheet somehow. I didn't try to, it just happened. Now everytime I open that workbook, it asks me if I want to run the macro, disable it, etc. How the hell do I get rid of the macro? It doesn't show up under tools, macros. And it apparently doesn't do anything either because I can disable it and nothing different happens. Who invented this system anyway? Thanks, V When you record a macro, a module is created to store the macro code. There are instructions here for removing the module that is causing the prompt to appear: http://www.c...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Viewing shared 2003 outlook calendar in 2007 Outlook
Shared calendars with colleagues who have 2003. I can see the calendar but not the info in the calendar. Nothing is provate marked from their side and is able to be seen by other 2003 users. is there a quick fix? ...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Excel Formula Help
I am setting up a basic excel spreadsheet and really have got no experience with excel. I want to do something real basic like if A2=laptop display $10 or if A2=desktop display $20 how do I do this? I think that if the list of options is more than a just a few, a =vlookup() function would work nicely. It may seem a little complex to start, but once you use it, you'll find tons of more reasons to use it. Debra Dalgleish has some nice instructions at: http://contextures.com/xlFunctions02.html BadSector wrote: > > I am setting up a basic excel spreadsheet and really have got no...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

Can you link 2 worksheets together?
Say i have one worksheet and on my second one I want to reference cells from the first one? is there a formula for that>? To create a simple link: Select a cell in the second worksheet Type an equal sign Select the first sheet Click on the cell that you want to link Press the Enter key. Alesha wrote: > Say i have one worksheet and on my second one I want to reference cells from > the first one? is there a formula for that>? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

creating nested formulas from drop down box
Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="...

passeord Protect for excel formula and VBA code
Hi, i have excel sheet its contain lot of formula and VBA(macro) code, its for used for user purpose, i need how to protect the formula and VBA at the time of user using. kindly let me know , kindly help me out i need user password : for user can upload the data in non restriction cell, admin password : can change any thing(change power) pls help me, Please ask on the Excel board. "deen" wrote: > Hi, > > i have excel sheet its contain lot of formula and VBA(macro) code, > > its for used for user purpose, > > i need how to protect the formula and ...

Active Task View under Tasks
Hello. Does anyone know how to get the "completed" tasks off of my "Active Tasks" view. It should only show those tasks that are not yet complete but for some reason it shows the upcoming as well as the completed tasks (i.e. those that are lined out). Any help would be appreciated. -Lumas >-----Original Message----- >Hello. >Does anyone know how to get the "completed" tasks off of >my "Active Tasks" view. > >It should only show those tasks that are not yet complete >but for some reason it shows the upcoming as well as t...

Tiebreaker in a Index formula?
I need a tiebreaker for a formula. Right now the formula reads: =INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0)) which works real well But when there is a tie it shows on one store twice instead of listing the two separate stores. I have two stores have the exact scores and it list the store with the lower store number twice. For example I have store 598 and store 698 both with a score of 100% but in the ranking of the stores it shows store 598 twice instead of 598 then 698. Is it possible to have a tiebreaker, with the index formula, that can list the stores in descending order, 598 first...

locking / unlocking cell in formula
E F 1 Do you own a guitar validation cell (yes/no) 2 If yes is it a Gibson 3 Is it a 5 string 4 Is it electric 5 Do you own a piano 6 Is it electric 7 Does it have 88 keys 8 Do you own a TV 9 Is it color 10 Is it an LCD 11 Is it 25" 12 Is it 32" 13 Is it 42" 14 Do you own a radio I would like to lock cells F2:F4 if F1 contains the word "no" I would also like to lock cells F6:F7 if F5 contains the word "no" I would also like to lock ce...

plz help in to creat formula of excel
hello to all i want to discuss my problem with you related to MS excel. i want to know or find out formula of excel which can help me in such way.... i want to make grade point of my college students in such a way.. for example.... marks grade point average 50 1 53 1.3 60 2 68 2.8 70 3 76 3.6 80 4 84 4 89 4 90 4 98 4 100 4 this is example the student who get 50 marks the GPA will be 1 and who get 80 marks or above 80 the GPA should be 4 GPA should not less than 1 or grator than 4 please tell me the formula so that i can make this GPA thanks a lo -- khushe --------------------------...

about treeview in view window
hi I am trying to display treeview in view window.But unable to do that. When we call CTreeCtrl &tree=GetTreeCtrl(); it is giving error that GetTreeCtrl is undeclared idenifier.So any one can tell ,how i have done this. I don't want to use App Wizard step 6 Base Class TreeView Class. Arbind your view window should be a CTreeView,GetTreeCtrl() is the member function of CTreeView,but not CView. "Arbind" <arbindb_tech@rediffmail.com> wrote in message news:OOePU1iXDHA.1644@TK2MSFTNGP10.phx.gbl... > hi > > I am trying to display treevi...