On Mar 4, 11:41*pm, John wrote:
Hello.

Is there a way to tellExceltoreferenceasheetin a formula based on a
variable like the month?

Currently I am working on a project where new data is added each month and
then is calculated on a summerysheet. Now every month the summery needs to
be updated to reflect the new month. So I want to be able to add the new
sheets to the workbook, then tellExcelto recalculate the data using the
most current month. Here is one of my calculations from the summerysheetfor
example.

=COUNTIF('Feb-08 Hires'!$J:$J,"New York")

What I am trying to do is figure a way to haveExcelchange the calculation
but base it off of the next month once thatsheetis added. Basicly tell it
to change the "Feb-08" part andreferencethe nextsheetonce its added,
which would be "Mar-08". Right now I have to go in and change every formula
by hand.

I have no problem renaming the sheets if needed. UsingExcel2003.

John, in your summary sheet enter the latest month's sheet name in a
cell. So enter "Feb-08 Hires" into cell F3 say.

Then change your formula (and similarly all other formulas in your
summary sheet) to

=COUNTIF(INDIRECT("'"&F3&"'!$J$J"),"New York")

Formulas will then automatically refence the sheet whose name you
enter into cell F3.

Just to make it easier to read, the formula is ... ( " ' " &F3& "
' ! ... after I add spaces.


Stefan