+ Reply to Thread
Results 1 to 6 of 6

Referencing Worksheets with a Text in the Name

  1. #1
    Registered User
    Join Date
    11-26-2015
    Location
    Idaho
    MS-Off Ver
    2013
    Posts
    5

    Referencing Worksheets with a Text in the Name

    Good Morning!
    I am trying to use a name manager for easier data retrieval from various worksheet. Since more worksheet is going to be added, I am wondering if there is an algorithm that reference to all worksheets that contains same text in the name.
    For an example, I want to expand my range to all worksheet containing term "January" in the tab name, so I can expand the range to include January Shift Report, January Inventory Report, January 22nd 2015 etc.

    Basically, in the name manager, I want the range to automatically include any extra worksheet with a specific text in the name.

    If possible.

  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: Referencing Worksheets with a Text in the Name

    Hi,

    Not entirely sure what you want to do with this named range even supposing you can create it. Can you explain the end goal. Are you for instance trying to Sum particular cells across the January... sheets?
    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
    11-26-2015
    Location
    Idaho
    MS-Off Ver
    2013
    Posts
    5

    Re: Referencing Worksheets with a Text in the Name

    Hello,

    Yes, the end goal here is to sum up a particular cell (A5 in this instance) from different worksheets. So in 'Summary' sheet, I would have totals from every A5s from every worksheet that is specifically related to month of January. The problem I am running into here is, there will be a large number of worksheet related specifically to the month and many more will be added. I cannot sum the particular cell from all worksheets because there are other months I am trying to keep separate. It is not helped that any worksheets specific to the month are all over the place, so you will not be seeing consecutive patterns in long list of worksheets, as you would have 'January Miscellaneous' tucked in between 'February Report' and 'July Revisions'. I did decide to utilize the name manager in this case, so I wish to know if there is a specific command which allows the range to cover worksheets with the term I wish to use, and the range to continue to expand automatically as I add in more worksheet related to "January".

    I can go the route vba if necessary, I do have to learn the program eventually anyhow.

    I hope I am clear with this one, but if you need me to expand a bit further, I would be happy to do so.
    Last edited by Bystander; 11-26-2015 at 09:03 PM.

  4. #4
    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: Referencing Worksheets with a Text in the Name

    Hi,

    It's not clear to me how you could do this with a named range so I'll be interested to see what others might come up with.

    I think I'd use a macro. e.g.

    Please Login or Register  to view this content.
    where 'SummarySheet' is the VBA code name for the sheet tab name "Summary". Don't confuse the VBA sheet code name with the tab name.

    Another less straightforward method would be to use a sum across sheets function like the following
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    where Start & End are the first and last sheet tab names immediately before and after the sheets you want to sum.

    Then the VBA task would be to move the sheet tab names that contain the text January together between the two blank (and hidden sheets) Start & End

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Referencing Worksheets with a Text in the Name

    A non-VBA approach might be to assemble a list of Tabs using a formula and then to use Index-Match, or similar, to copy only those containing a keyword, "January" or whatever, use that list to create a named range and use it to add up all the relevant A5s.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Referencing Worksheets with a Text in the Name

    Here's a sample sheet that I put together to illustrate the point. As you add sheets, therir name will appear in the green column. If they contain the keyword, they will also appear in the beige column. Any values in A5 will be summed in column A. Happy toexplain the process, if this meets your needs.

    ENABLE MACROS on opening. There's an old inbuilt Excel 4.0 function at work in the background that requires macros to be enabled.

+ 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. Referencing columns text across separate worksheets
    By musicnl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-29-2015, 09:22 AM
  2. [SOLVED] Referencing Across Worksheets
    By forexcellence13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2013, 02:06 AM
  3. Consolidating worksheets and then referencing the worksheets themselves in a new column
    By zed commander in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2012, 05:14 AM
  4. Referencing other worksheets?
    By ajcasey88 in forum Excel General
    Replies: 8
    Last Post: 02-28-2012, 08:15 PM
  5. Referencing Worksheets
    By Cincy in forum Excel General
    Replies: 4
    Last Post: 07-19-2006, 04:05 PM
  6. Referencing worksheets
    By [email protected] in forum Excel General
    Replies: 3
    Last Post: 05-13-2006, 09:40 AM
  7. Referencing worksheets
    By NewExcelGuy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2005, 01:05 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