+ Reply to Thread
Results 1 to 5 of 5

Worksheet_Name! based on Cell Value

  1. #1
    Registered User
    Join Date
    12-20-2019
    Location
    South Dakota
    MS-Off Ver
    365
    Posts
    50

    Worksheet_Name! based on Cell Value

    I have a worksheet template that I am constantly adding new tabs to; but I want a summary tab that references all tabs, and can easily add a reference to tabs as added.
    A majority of the tabs are all identical; literally made as copies of one another for each day of data entry. I then want a summary tab that is pulling four values from each tab to display. Then as I end up continuing to copy additional data entries per day, I want to be able to easily expand the summary tab to include the additional sheets.

    Since each day is a different day of data entry, then they are all named based on the day's date (e.g. 2022-06-01, 2022-06-02, etc.)
    On the summary tab; I can have these organized out in columns starting by date. But the values underneath would be ='2022-06-01'!$O$33, etc. I would like the worksheet_name! '2022-06-01' to instead reference the values in my top row, where that same value is also input. But doing =C2!$O$33 does not work. Is there a way to extract the value of the cell to utilize in place of the worksheet_name! in an excel formula? This way as I keep adding tabs, all I need to do is copy-paste C2:C4 into __2:__4 and update the date in __2 for it to reference the other values accordingly.

  2. #2
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    Microsoft Office 365
    Posts
    1,067

    Re: Worksheet_Name! based on Cell Value

    So normally you are going to utilize an INDIRECT() formula.
    The problem you are going to have is that your date in C2 is not actually 2022-06-01 it is converted into a number 44713. What you will need to do is change all the cell formats in row C on your summary tab to TEXT. Then you can utilize the below formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.





    EDIT:

    Ignore above you actually dont need to change the format of your dates, just use the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by dosydos; 06-23-2022 at 01:23 PM.
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,000

    Re: Worksheet_Name! based on Cell Value

    C1=C1

    A3 ='2022-06-01

    B3 =INDIRECT("'"&A3&"'!"&$C$1)


    I would add all data in 1 sheet, with the date in column A.

    After that a pivot table to analyse the data.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    12-20-2019
    Location
    South Dakota
    MS-Off Ver
    365
    Posts
    50

    Re: Worksheet_Name! based on Cell Value

    Works. Thanks.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,000

    Re: Worksheet_Name! based on Cell Value

    Glad I could help.

    Thanks for marking the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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] Delete Rows based on cell value, highlight based on cell value and keep deleted data
    By jaredli in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-21-2020, 06:37 AM
  2. Replies: 0
    Last Post: 08-05-2020, 10:55 AM
  3. [SOLVED] VBA code to duplicate work sheet based on cell value and rename based on cell value
    By NASANASA in forum Excel Programming / VBA / Macros
    Replies: 51
    Last Post: 03-12-2020, 07:54 AM
  4. Replies: 2
    Last Post: 09-02-2018, 01:09 PM
  5. Replies: 6
    Last Post: 01-23-2018, 04:49 AM
  6. Color Cell Based on Specific Text in Other Cell and Sorting Based on Results
    By kre30a in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2016, 07:51 PM
  7. Delete row based on cell FORMAT (color, font, etc) (not based on cell value)
    By trobey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2012, 10:39 AM

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