Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet names in formulas (97)

    Hi

    I have a collection of data that extends across many days [months now actually] where I store the daily detail in a straight table format on a sheet which is named for the day [ie "02-05-2002" for May 2nd dd-mm-yyyy format]. I am then referencing and counting various data occurences on a data summary sheet.

    What I would like to do is to be able to drag/fill the formulas on the summary sheet as I add new days data sheets to the workbook. At the moment i have to drag the cells and then edit them to update the sheet names to point at the correct data.

    The date appears in a cell on the data sheet. Is there a way to reference the date and use that to recognise the correct data sheet? An example of the formulas I have is :-

    =COUNTIF('02-05-2002'!$J:$J,E$2)

    In this instance I am counting occurrences of data for a particular state on the days worksheet. I've tried a few ways using different function combinations but I can't identify a solution. I'm sure there is one.

    Thanks

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

    Re: Sheet names in formulas (97)

    I was able to get it to work in XL2K by putting this in cell A2 on the summary sheet:

    <pre>="'"&TEXT(NOW(),"dd-mm-yyyy")&"'!$J:$J"
    </pre>


    And then using this to count:

    <pre>=COUNTIF(INDIRECT(A2),$E2)
    </pre>


    I have not been able to get it to work in one formula.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet names in formulas (97)

    Thankyou Legare.

    I had tried something similar but the INDIRECT() was the piece of the puzzle that I was missing. That now works perfectly.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet names in formulas (97)

    perhaps this example can help you
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet names in formulas (97)

    Thanks Servando.

    I assume that the Array formula is necessary to make the Indirect work in a single cell rather than Legare's option to use another cell to contain the reference which I have currently implemented. My experience previously has been that Array formula's gave me problems. I have only used them a few times so I'm not as comfortable with them as the other option.

    Thank you for the assistance though. It is much appreciated.

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

    Re: Sheet names in formulas (97)

    Funny enough it does work if you separate the cell reference from the sheet reference:

    In A1:
    ="'"&TEXT(NOW(),"dd-mm-yyyy")&"'!"
    In A2:
    "A:A"
    In other cell:

    =COUNTIF(A1 & A2,"=" & $E$2)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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