Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Dynamic Sheet Name (Excel XP)

    Assume the following formula in cells a1=Jan!$d$4, b1=Feb!$d$4,c1=Mar!$d$4,d1=Apr!$d$4. This continues across row 1 for a total of twelve columns with the last formula =Dec!$d$4. I have hundreds of formulas with this pattern to create and copy across rows on a sheet and then must create the same type of formulas on different sheets. Is there any way that I can dynamically name the sheet name so that when I copy from a1 across the other 11 columns, that the sheet name is correct (i.e., formula in column b is for sheet feb; formula in column c is for sheet mar)? Right now, I am copying the a1 formula across the columns and then manually changing each of the other 11 cells to the proper sheet name since the cell location is absolute. I have thousands of these to do and was thinking there has to be a better way. THANKS.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Name (Excel XP)

    - Type Jan into cell A1
    - Drag the fill handle to the right untill you get to Dec
    - in A2 type this formula:

    ="A=" & A1 & "!A1"

    The result will be (in A2): A=Jan!A1

    Now select the 12 cells on row 2 and drag its border whilst holding the right mouse key on top of A1:L1 and release.
    From the menu that appears select "Copy Here as values only".

    Finally, hit control-H and find A= and replace with =
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dynamic Sheet Name (Excel XP)

    How about this in A1 and then copy it?
    <pre>=INDIRECT(TEXT(DATE(2005,COLUMN(),1),"mmm")&" !D4")</pre>


    Steve

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Name (Excel XP)

    Steve,
    This formula gives me a #REF! error. Any ideas? THANKS.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Name (Excel XP)

    That says you don't have a sheet in the workbook named Jan, or Feb, or whatever matches the column you put that formula in.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Name (Excel XP)

    Steve,
    How can I adjust your formula to start in B1 and not A1. Column A is used for descriptions and the twelve months of monthly data starts in column B and moves by month to the right. I have tried moving the "1" in your formula to zero but that does not work. I read through the on-line help, but to be honest, I still don't understand the indirect formula and its options. Thanks for your patience. Thanks also to Legare..he was right. Since I was so unfamiliar with the indirect function, I wanted to "play with it" in an empty workbook and not the production workbook that contained the Jan, Feb, etc, worksheets. Take care.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dynamic Sheet Name (Excel XP)

    Hans,
    Yes, I replied to the wrong post...sorry. Thanks for the explanation. Take care.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Sheet Name (Excel XP)

    I assume that you meant to reply to <post#=474907>post 474907</post#> by Steve. The DATE function has syntax DATE(year, month, day). Steve's formula uses DATE(2005,COLUMN(),1), i.e. year=2005, month=COLUMN() and day=1. The COLUMN() function returns the column number of the cell containing the formula, i.e. 1 for column A, 2 for column B etc. To make column B correspond to the 1st month, you must subtract 1 from the column number: DATE(2005,COLUMN()-1,1), so the formula becomes

    <code>=INDIRECT(TEXT(DATE(2005,COLUMN()-1,1),"mmm")&"!D4")</code>

Posting Permissions

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