Make a formula - list automatically

Is it possible with Excel 2000 to automatically
make a list of all the formulas you are using in
your spreadsheet to print them.
Thanks...Steve

--
Steven E
-----------------------------------------------------------------------
Steven E.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1512
View this thread: http://www.excelforum.com/showthread.php?threadid=26758

0
1dt8tb (1)
10/8/2004 1:40:43 PM
excel 39879 articles. 2 followers. Follow

2 Replies
480 Views

Similar Articles

[PageSpeed] 46

This subroutine makes a new sheet (Formulas in Sheen N) in which all 
formulas are listed together with cell reference and value. Not mine-own but 
I have lost the reference to the author but I thank her/him again for this.

best wishes

-- 
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

Sub ListFormulas()
    Dim FormulaCells As Range, Cell As Range
    Dim FormulaSheet As Worksheet
    Dim Row As Integer

'   Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

'   Exit if no formulas are found
    If FormulaCells Is Nothing Then
        MsgBox "No Formulas."
        Exit Sub
    End If

'   Add a new worksheet
    Application.ScreenUpdating = False
    Set FormulaSheet = ActiveWorkbook.Worksheets.Add
    FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

'   Set up the column headings
    With FormulaSheet
        Range("A1") = "Address"
        Range("B1") = "Formula"
        Range("C1") = "Value"
        Range("A1:C1").Font.Bold = True
    End With

'   Process each formula
    Row = 2
    For Each Cell In FormulaCells
        Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
        With FormulaSheet
            Cells(Row, 1) = Cell.Address _
                (RowAbsolute:=False, ColumnAbsolute:=False)
            Cells(Row, 2) = " " & Cell.Formula
            Cells(Row, 3) = Cell.Value
            Row = Row + 1
        End With
    Next Cell

'   Adjust column widths
    FormulaSheet.Columns("A:C").AutoFit
    Application.StatusBar = False
End Sub




"Steven E." <Steven.E..1dt8tb@excelforum-nospam.com> wrote in message 
news:Steven.E..1dt8tb@excelforum-nospam.com...
>
> Is it possible with Excel 2000 to automatically
> make a list of all the formulas you are using in
> your spreadsheet to print them.
> Thanks...Steven
>
>
> -- 
> Steven E.
> ------------------------------------------------------------------------
> Steven E.'s Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=15123
> View this thread: http://www.excelforum.com/showthread.php?threadid=267589
> 


0
bliengme5824 (3040)
10/8/2004 2:08:15 PM
Bernie

John Walkenbach's creation.

Can be found at http://j-walk.com/ss/excel/tips/tip37.htm

Gord Dibben Excel MVP

On Fri, 8 Oct 2004 11:08:15 -0300, "Bernard Liengme"
<bliengme@stfx.TRUENORTH.ca> wrote:

>This subroutine makes a new sheet (Formulas in Sheen N) in which all 
>formulas are listed together with cell reference and value. Not mine-own but 
>I have lost the reference to the author but I thank her/him again for this.
>
>best wishes

0
Gord
10/8/2004 2:55:09 PM
Reply:

Similar Artilces:

Word 2007-Pinning documents to most recent list
I've pinned several documents to my most recent documents list but they don't always show. For example, I have about 5 documents that I have pinned and I'm showing 20 new documents when I click the Office button in Word. If I have worked recently with 25 or 30 documents, but not one of the 5 that are pinned, then the most recent 20 show, and that list doesn't always include the 5 that are pinned. Do I have a setting wrong somewhere? Or is this a known issue? Thanks! If you are showing 20 documents in the list and you have worked with 20 documents other ...

Making data span 2 columns
Hi, I'm new to this community and new to the RMS system. I am trying to modify the PrintTransactionDetails sub in the receipt.xml to be 2 columns and put the entry.description information onto another row. The person who writes the descriptions likes to be wordy and I need the entire 40 columns in order to hold the text. I changed the 3 columns into 2 with a format of 71% for the item number or description and 29% for the sales amount. The description and a null field occupy the first row and the item number and amount occupies the second row. I have the transaction d...

contacts not automatically appearing
when I type in the beginning of an e-mail some of my contacts will not appear in the list of people who start with a certain letter That is known as the auto-complete feature. Windows Mail does not auto-complete from your contacts. It uses a separate list of the last 29 recipients you've sent mail to. The newer replacement email program, Windows Live Mail, uses the standard contacts list for auto-completion. You are encouraged to upgrade to the newer WLM program: http://download.live.com/wlmail =20 --=20 Gary VanderMolen, Microsoft MVP (Mail) http://mvp.support.microsoft....

How do you make a chart with right angles only?
I have a set of data with times and a bunch of 0's and 1's. For example, at 1:30 p.m., I have a 0....2:00 p.m., I have a 1...2:30 p.m. I have a 0, etc. I have my 0's and 1's set up on the vertical axis and the times along the horizontal axis. I want my chart to show horizontal and vertical lines ONLY. IE: I want a point at "0" at 1:30 p.m., then I want a horizontal line over to 2:00 p.m., and then I want a line going straight up to "1", and then I want a horizontal line over to 2:30 p.m., and then I want a vertical line straight down to "0&qu...

Exporting Members on a distribution list
Folks here is a question, you know how you select a distribution list and then you click on Properties you can see all the member name in full. I want to know how I can export the names of people who are part of that distribution list to a excel or text file? Is there a way, I don't want to do this in AD only because in AD it only shows the users login name as oppose to their fullnames. Thanks in Advance. WooYing <wooying@nospam.comcast.net> wrote: > Folks here is a question, you know how you select a distribution list > and then you click on Properties you can see...

Is there a way to turn off the prompts of a make table query?
I merely want to update the table to be used as an export to Excel using TransferSpreadsheet. Each time it prompts for deleting old records and adding new records. Is there a was to turn off the prompts? Bottom line - I want to create an Macro to export the output of a query to an Excel spreadsheet. BobC, DoCmd.SetWarnings False 'Off Your TransferSpreadsheet line here DoCmd.SetWarnings True 'On -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "...

Mandatory and optional formula
I am trying to figure out how to write a formula where certain things are mandatory and others are optional. Say I have cells A1, A2, A3, A4 and A5. A5 is where I want the result that will either be "N" or "PRE". I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5 will equal "N". I assume there is a way to nest an if/and with an "if/or" but can't seem to get it to work! "Patrick"...

how do you print screen sent emails list as it appears
i want to print off the list of sent emails just as it appears on the screen when i have clicked on sent items. i tried to highlight 1st and then last item holding down shift but this only started printing each idiv email cover page as if opened giving me 60 pages ea email (i stopped it) not just the list as it appears;like i want- approx 2 pages. how do i do this-its sort of a print screen i want to accomplish. thanks Hold down "Alt" & press "Prnt Scrn". You can then paste into Word. >-----Original Message----- >i want to print off the list of sent email...

Making a template
Is there a way to create and save an e-mail template to be used when you want to send the same message to several people, but at different times. I thought one of the older versions had this capability, but I can't figure it out in Windows Live Mail 2009. Thank you. Open a new message window and create the message that will always be the same. You can enter addresses that will always be the same as well. Now, File | Save As and save it to the Desktop or elsewhere as a .eml file. When you want to use this prepared message, just click on the icon that you just created where...

Sales Transactions list displays wrong transaction on zoom
1. Click on 'Customers' in Sales Lists on the Navigation pane 2. Search for a specific customer 3. Highlight the customer line, then Actions>View Transactions 4. Double click on a line item on the Sales Transactions screen The wrong document is shown in the Sales Transaction Inquiry Zoom window. It's like it randomly selects one of the other line items - sometimes it repeatedly displays the same item no matter which one you select; other times it picks other ones, and other times it shows the correct one. Anyone else experience this? -- GP 9.0, SP1 Bud, I have never see...

Automatic virus scan
how do you stop Excel from requesting a virus scan when you open up the program You need to tweak the settings in your anti-virus software. But do you really want to do that? That would only be acceptable if you NEVER get Excel files from anyone else. Carlos "VinnyG" <VinnyG@discussions.microsoft.com> wrote in message news:61DA150F-8A1D-4C3C-9A3A-453D4F9B0FEB@microsoft.com... > how do you stop Excel from requesting a virus scan when you open up the program If you are writing about the macro alert when you open up a spreadsheet, go to Tools, Options, Security, Macro ...

List of where queries are used?
I have an access database which has many queries which are leftover from various tests and temporary data extract needs. Is there a tool which can tell me which queries are used in all reports, queries, macros, or modules in the database? Hi Joseph, You might want to take a look at the Total Access Analyzer (< http://www.fmsinc.com/MicrosoftAccess/BestPractices.html>). Or you can use Access's own Documenter. Run it on all the objects in Access with maximum reporting settings, and then export/save it to a file of some sort that can be searched (text, PDF, Wo...

junk mail list of sender
I saw MS updating office2003 outlook with a better junk filter and was wandering if if they are doing the same to office 2000 or XP. Office 2003 has a feature to export or import email addresses of junk mail senders and was wandering if office 2000 has the same feature or if anybody know the name of the file that Microsoft designated when you add junk mail sender in office 2000. JM Jose Matus <anonymous@discussions.microsoft.com> wrote: > I saw MS updating office2003 outlook with a better junk > filter and was wandering if if they are doing the same to > office 2000 or...

Automatic deductions for utility payments
We are using GP 7.50. We have it set up that most of our utility payments are automatically deducted from our checkbook on a monthly basis. How do I enter these automatic deductions into Great Plains? I do not need a check to be issued but somehow need to enter these transactions. Any input would be quite helpful.... Thanks Barb use manual payments "BK" <BK@discussions.microsoft.com> wrote in message news:713115E0-F27B-4401-8B56-606D6E503003@microsoft.com... > > We are using GP 7.50. We have it set up that most of our utility payments > are automatic...

using a popup to make percenatges on selected cells
Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes hi Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 ...

Automatic printing
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Why does my mac automatically print out a document everytime I save it in word and how can I stop it please? Thank you ...

Reviewing the formula =+AD11/$Z$20, what does it mean?
Reviewing excel spreadsheet, what function is being formed by the following formula =+AD11/$Z$20 perform i.e, what does it mean? It is dividing the value in cell AD11 by the value in cell Z20. The $'s around the Z20 mean that if you drag or copy that formula to another cell, the reference of AD11 will change but Z20 will remain (an absolute reference). -- Regards, Dave "lance559" wrote: > Reviewing excel spreadsheet, what function is being formed by the following > formula =+AD11/$Z$20 perform i.e, what does it mean? This could also be written without the &q...

If I type a formula into excell, and forget the "=", the equal si.
If I type a formula into excell, and forget the "=", the equal sign should be added automatically. Your opinion ? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Will C Holmes" <Will C Holmes@discussions.microsoft.com> wrote in message news:E994F354-F6A8-4A99-B095-D998FDDC9950@microsoft.com... If I type a formula into excell, and forget the "=", the equal sign should be added aut...

Don't Want Global Contact List
Using Windows 7 with Outlook 2003 at work. On a brand-new computer, when addressing an e-mail and clicking the "to" box, the global contacts come up and I must choose "contacts." Is there a way to make "contacts" the default rather than "global" so that I don't have to choose this each time? Thank you for your help! Choose Tools | Address Book, then from the address book dialog, choose Tools | Options. You should see a control there for setting which address list you want to display first. -- Sue Mosher, Outlook MVP Author ...

Making a One Column Directory from Two Columns
I'm using Excel 2003. I have a 2 column list of chapter members' names in one column and their schools inthe other. There are over 1800 entries. I want to end up with a single column sorted by the schools, with the name of the school in a row, followed below by the names of the graduates in separate rows. then the next school, etc. Like Emery-Riddle University Jones, Frank Smith, Fred Franklin University Adams, Susan Baker, Sam Is there a formula that can do this or do I have some (a lot) manual formatting to do? Thanks, Dennis Let's see. It's going to sound more...

excel formulas #10
how do i create a formula using the same cell reference (locking that cell) to be calulated in all formulas using fill down short cut? Hi John! Reference the cell (eg) $A$1 See: Chip Pearson http://www.cpearson.com/excel/relative.htm -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "John" <jallen@novurania.com> wrote in message news:4DE2EB3B-9FA4-4CC0-B2CC-E6296EB364EC@microsoft.com... > how do i create a formula using the same cell reference (locking > that cell) to be calulated in all formulas using fill down short > cut? ...

highlighting area used for formula
d1=sum(a1:b2), d3=sum(a3:b4)+c3 I want excel to highlight (fill colour) the cells which are included in sum formula. How can I do this? Some time when I double click the divider of two rows (faint line between two rows where cursor changes double headed) to autofit the height of row, it does not change automatically the height of that row containing a particular text. I have to manually drag it to adjust the height the row containing text. Why it is like this? Paste this function into a standard module (adding it (Hasformula) to your active functions list). Highlite the range, say D1:D3, ...

Combo Drop Down List Alignment
If the Dropdown list is wider than the Combo Box is it possible to align it with the right hand side of the Combo instead of the left, effectively it sticks out to the left not right..............Thanks for any Help!....Bob Bob wrote: > If the Dropdown list is wider than the Combo Box is it possible to > align it with the right hand side of the Combo instead of the left, > effectively it sticks out to the left not right..............Thanks > for any Help!....Bob It will always drop to the right unless the screen does not have enough room to the right in which case it will...

Close msgbox after making selection
I make use of a msgbox with Yes/No buttons fairly frequently, like: answer = msgbox("Do you want to proceed?", vbyesno) After clicking on the desired button the message box window remains open. Is there a way to force closing this window as soon as the variable is read? John Keith kd0gd@juno.com Is this a message box that was created with a UserForm? The standard message box automatically closes when a button is clicked. If it is the standard message box and is not closing, there could be a software problem in your system. Is this in Excel, if so, what versio...

in excel, how can i dedupe a list of numbers?
I have a column of several thousand numbers that i need to distill to one instance of each number - is there a dedupe function in excel Hi Catherine, Use Advanced Filter (on the Data Menu). If you are not familiar with this feature, Debra Dalgleish has a good tutorial with screen shots at: http://www.contextures.com/xladvfilter01.html --- Regards, Norman "catherine" <catherine@discussions.microsoft.com> wrote in message news:F4B3C0F5-872D-47E4-A754-103539EE17F6@microsoft.com... >I have a column of several thousand numbers that i need to distill to one...