I have two workbooks that are linked in many ways. I'll call them
workbook A and workbook B. Workbook A is a list of participants in a
fundraising weight loss challenge and information about the
competition - i.e. start/end date, weigh in coordinator, department,
etc.

Workbook B contains statistics for each of the participants as their
weight is tracked on a weekly basis - mainly calculates their rank
amongst other participants because in the end there will be a winner.

This workbook will be used for several challenges, throughout the
corporation. There are 9 departments and each department will be using
the workbooks for their own fundraising challenge. They will have
different start/end dates. Thanks to someone in my last post, i was
able to create a function that will enable someone to enter a start
date in workbook A and this will automatically update the weigh-in
dates (the tab names in workbook B). I am trying to make this as easy
to use/fool proof as possible. So when it is distributed, there's
minimal user intervention.

What i'm trying to do now... there are references in Workbook A, that
reference the tab names in Workbook B. Now that 1. users can select
ANY start date 2. the tab names can change in workbook B... my
question is, is there a way to reference the sheet by a fixed sheet
name instead of the name that i chose for it? Is there a way in code
that i can call these sheets, by sheet1, sheet2, etc. without changing
the name of the sheet (the weigh-in date) as the user sees it. Note:
There are only 14 sheets in workbook B. This is just to make it
easier for coding... the competition will only be 13 weeks long (the
first spreadsheet in workbook B tracks their initial weigh-in)...

any assistance provided will be appreciated. thanks :o)