+ Reply to Thread
Results 1 to 8 of 8

Extract formula data from cell J10 on 150+ sheets then list those formulas on a report tab

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    NC
    MS-Off Ver
    2010
    Posts
    25

    Extract formula data from cell J10 on 150+ sheets then list those formulas on a report tab

    Good evening all,

    I need to extract the formula data in cell J10 across 150+ tabs of an excel file & list those formulas to a report tab.

    The formulas are different in J10 from tab to tab so I can't do a global change. In addition, some tabs don't have a formula in J10 at all.

    I need to modify the formulas slightly & to not miss any tabs, my thought was to have the formulas extracted in a list format on another tab, I can do the find & replace function.

    I've never done VB before, so if that's what you are going to suggest, I'll need a little bit of direction on how / where to enter it.

    Thanks !!!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract formula data from cell J10 on 150+ sheets then list those formulas on a report

    Hi, welcome to the forum

    I know this does not answer your question but - 150 sheets? All I can say is - wow. Are they all data input sheets...different months...different depts...why so many?

    Perhaps streamlining your workbook will mak adjusting formulas easier. What do you have on each of those sheets?
    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

  3. #3
    Registered User
    Join Date
    01-09-2015
    Location
    NC
    MS-Off Ver
    2010
    Posts
    25

    Re: Extract formula data from cell J10 on 150+ sheets then list those formulas on a report

    Yes, it is a lot of sheets....

    A little bit of background - I work in a textile mill & we do installations in hotels for window draperies. So many options for treatments - sheers, blackouts (to blackout the light in the room), side panels (that go on the sides of the windows) etc. We have a measures tab that we manually enter the measurements that the installer took. Copy them to the measures 2 tab so they can be sorted by window size, room type, etc.

    Then go to the appropriate tab for what is being installed, copy down the formulas in row 10 for how many rows there are in the measures 2 tab. Therefore, starting in row 11 will be the 1st row of the measures 2 tab with deductions applied.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract formula data from cell J10 on 150+ sheets then list those formulas on a report

    Sounds really involved and manual

    Can you upload a (small) clean workbook, showing what you have, and what you want?

    Out of curiosity, how on earth do you navigate that many sheets?

  5. #5
    Registered User
    Join Date
    01-09-2015
    Location
    NC
    MS-Off Ver
    2010
    Posts
    25

    Re: Extract formula data from cell J10 on 150+ sheets then list those formulas on a report

    Sure I can upload it, but on Mon.

    We have a config tab setup with hyperlinks to the each sheet for the type of layout there is.

  6. #6
    Registered User
    Join Date
    01-09-2015
    Location
    NC
    MS-Off Ver
    2010
    Posts
    25

    Re: Extract formula data from cell J10 on 150+ sheets then list those formulas on a report

    I attached a stripped down version of my workbook.

    It includes a summary tab that I manually entered what I want to see.

    ms deductions stripped.xlsx

  7. #7
    Registered User
    Join Date
    01-09-2015
    Location
    NC
    MS-Off Ver
    2010
    Posts
    25

    Re: Extract formula data from cell J10 on 150+ sheets then list those formulas on a report

    I was finally able to show the formulas from multiple sheets for cell J10 on a separate tab !

    True, it may be a lllooooooonnnnnggggg way, but it works !



    First, I had to create a list of the sheet names on a summary tab. Watched a youtube video (can't remember the author) & made a sheetlist (defined name) =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

    Then I used the index function for a formula as follows: =INDEX(sheetlist,ROW())

    Enter that formula in A1 & then copy it down for as many tabs as I have (150+ phew !)


    Now that I had the list of names, I need to see the formulas from cell J10 of EACH tab (not the results, just the formulas)....


    I entered the code per user joeu2004 (thanks !!) as follows:

    Function myformula(r As Range) As String
    Application.Volatile
    If r(1).HasArray Then myformula = "{" & r(1).Formula & "}" _
    Else: myformula = r(1).FormulaArray
    End Function


    Then entered the following formula in B1: =myformula(INDIRECT("'" & A1 &"'!J10"))

    And copy down that formula for the same number of cells as column A

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract formula data from cell J10 on 150+ sheets then list those formulas on a report

    Thank you for sharing the answer with us, and great job on solving it

+ 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. [SOLVED] Using either VBA or formulas to create a filtered list report from data sheet
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-24-2014, 12:38 PM
  2. Replies: 0
    Last Post: 04-21-2014, 03:42 PM
  3. Replies: 15
    Last Post: 11-11-2013, 08:21 PM
  4. VBA script to extract simple data from test list to create report.
    By weekendMajor in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-26-2012, 02:52 AM
  5. Formula to extract Employee work schedule report from "date of hiring list"
    By ravikiran in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-13-2010, 05:56 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