+ Reply to Thread
Results 1 to 10 of 10

Dynamic "List Formulas" Sheet

  1. #1
    Registered User
    Join Date
    08-28-2017
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    40

    Dynamic "List Formulas" Sheet

    When you use the "List Formulas" feature from a Pivot Table / Chart, it generates a new Sheet in your workbook.

    A simple question that I haven't found an answer to, via Google:

    How can I make that sheet more dynamic? i.e. if the formulas change in my Pivot Table, then the sheet should change.

    OR at least, when I click "List Formulas" to generate the sheet again, can I just overwrite the same sheet instead? The default seems to be to generate a new sheet...

    I have a feeling the latter is going to be have to be resolved with a macro -- if it has to go down this route.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic "List Formulas" Sheet

    Is the PT on a separate sheet of its own.

    Almost certainly this automation will require a few lines of macro code - nothing too heavy

    Upload the workbook and no doubt we can help further. Identofy the PT if there's more than one.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-28-2017
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    40

    Re: Dynamic "List Formulas" Sheet

    I'll PM anyone that can help. I've done my best to get the workbook down to as little info as possible, but I'm still not comfortable posting the file for download on a public forum.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Dynamic "List Formulas" Sheet

    Please do not go to PM - it is against the spirit of a public forum, and is in fact not allowed here. Everything should stay here in this thread for the benefit of all. As you say, this is a public forum, so you cannot trust that anyone here will respect the privacy of your data. Now, this is not to say that none of us can be trusted, it's just that we can't guarantee it. Having said this, a small anonymised workbook should not compromise anything.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    08-28-2017
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    40

    Re: Dynamic "List Formulas" Sheet

    Okay. Please see attached.
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic "List Formulas" Sheet

    See attached. When you select the PT sheet i.e. activate it the Activate event will trigger the listing of formulas
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-28-2017
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    40

    Re: Dynamic "List Formulas" Sheet

    Thanks, but a couple issues:
    1. It automatically takes me to that new sheet, so I can't actually access the PT sheet anymore -- I could probably solve that with a small macro tweak.
    2. I don't want a new sheet for every time I access the PT sheet. I want the original one to be overwritten, and the windows shouldn't switch to that sheet.

    Almost there with that -- and I'm sure I could solve it on my own with a few hours of going through the macros; I know it's fairly simple VBA, but I'm just not very quick with it...

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic "List Formulas" Sheet

    Try this version

    It first creates a new sheet that lists the formulas, then deletes the data on the current List Formula sheet, copies the new list of formulas from the new sheet then deletes the new sheet.

    This is the macro
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic "List Formulas" Sheet

    Glad to have helped and thanks for the rep.

  10. #10
    Registered User
    Join Date
    12-25-2018
    Location
    nyc
    MS-Off Ver
    2018
    Posts
    4

    Re: Dynamic "List Formulas" Sheet

    Great advice given here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. [SOLVED] Copy values from sheet"list" to "invoce" on trigger
    By perko121 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-11-2017, 11:33 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. Replies: 1
    Last Post: 03-09-2016, 12:17 PM
  5. Replies: 1
    Last Post: 02-20-2015, 01:13 PM
  6. Replies: 1
    Last Post: 01-16-2015, 04:44 AM
  7. Dynamic List help...Macro to autofill a sheet based on "checkbox" criteria
    By drumbok5 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2012, 10:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1