+ Reply to Thread
Results 1 to 3 of 3

Populate sheet name based on a formula...

  1. #1
    Registered User
    Join Date
    05-10-2007
    Posts
    7

    Talking Populate sheet name based on a formula...

    I have a file I have been working on that includes different sheets for each day of the week with formulas that flow to another sheet that is kind of like a tally sheet for two weeks.

    My issue: Right now I have each sheet numbered for the sheet name "1" through "14". It would be great to have the sheets name themselves as a date based on a date entered on the tally sheet, i.e. "12/07" as the name for sheet one through "12/20" for the last sheet name, without having to actually type the dates in for each sheet.

    Is there a way to input a formula somehow to do this? I really am trying to avoid macros. I am setting this up for users who are not particularly Excel savy...

    Any help or ideas would be greatly appreciated.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Sheet names would be manually set, or controlled by some VB code, I believe those are your only two options to do what you've asked.

    BUT, maybe you're coming at this from the wrong end. You could make your Tally Sheet use an INDIRECT reference to refer to sheets that haven't been created yet. As long as your users DO name their sheets properly (a training issue), the tally sheet would work properly. Plus, the tally would serve as a sample of what the dates SHOULD look like.

    On the tally sheet, create a reference column with your desired sheetnames
    Please Login or Register  to view this content.
    Now, to the right in the same row, you put in INDIRECT references to cells. So, let's say the first thing you want from the sheet 12/1 is the answer in cell B10. The next formula in the row with 12/1 would be:

    Please Login or Register  to view this content.
    That formula is constructing the reference from dates in column A. Pretty cool. If a sheet hasn't been created yet by a user to make the INDIRECT call complete, it just returns A ZERO VALUE. When the user creates the sheet and starts filling it in, the tally starts working on its own.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-10-2007
    Posts
    7

    Thumbs up Thanks so much!

    Yes, I will look at it from that angle. I had my users type in a date on the sheets for each day. Instead they can type it in the sheet name and the "date" field on the sheet can autopopulate based on that sheet name as well as the tally sheet taking it's date for each day from the sheet names.

    Thanks for thinking outside the box. You are awesome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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