Sheet names in formulas (97)

[Heideho]

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

[LegareColeman]

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.

[Heideho]

Thankyou Legare.

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

[Servando]

perhaps this example can help you

[Heideho]

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.

[pieterse]

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)