Hi John,

I don't think there is any way of doing what you want without using VBA but
do you realize that you can use Find/Replace to replace the worksheet name in
all the formulas on the worksheet. Just set the options to Lookin formulas
and leave the Match entire cell contents unchecked.

If you initially use the Find Next and then Replace a few times before
clicking Replace All then it gives you an opportunity to check that you are
finding and replacing the right ones.

--
Regards,

OssieMac


"John" wrote:

Hello.

Is there a way to tell Excel to reference a sheet in 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 summery sheet. 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 tell Excel to recalculate the data using the
most current month. Here is one of my calculations from the summery sheet for
example.

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

What I am trying to do is figure a way to have Excel change the calculation
but base it off of the next month once that sheet is added. Basicly tell it
to change the "Feb-08" part and reference the next sheet once 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. Using Excel 2003.