Link to the FIRST worksheet
 

I added a summary worksheet at the end of the sheets to summarize some info.
I will name the range and use it to pull in info from an Access db. I want
to link this to the FIRST worksheet only. What happens is every fiscal year
they add a new worksheet in the first position, so this year ws1 is 'MD
2010', but in a few months they will add 'md 2011', and I want my summary to
link to the worksheet in the first position, not any particular name. (so
the cell link ='md 2010'!B2 won't work) thx

Link to the FIRST worksheet
 

After the new sheet is added, you can highlight all the cells on the
summary sheet and use Find & Replace (CTRL-H):

Find what: MD 2010 (or whatever your current first sheet
is called)
Replace with: MD 2011 (or the name of your new sheet)
Click replace all

Hope this helps.

Pete

On Apr 27, 4:23*pm, Maarkr wrote:
I added a summary worksheet at the end of the sheets to summarize some info. *
I will name the range and use it to pull in info from an Access db. *I want
to link this to the FIRST worksheet only. *What happens is every fiscal year
they add a new worksheet in the first position, so this year ws1 is 'MD
2010', but in a few months they will add 'md 2011', and I want my summary to
link to the worksheet in the first position, not any particular name. *(so
the cell link ='md 2010'!B2 won't work) thx

Link to the FIRST worksheet
 

Take a look at the indirect function. It will allow you to put a worksheet
name in one cell and use that as part of the cell reference.
=indirect("'" & A1 & "'!B2")
will use the value from Cell A1 where you put the sheet name.

Note that this formula is not dragable and that it is volatile so it has
high calculation overhead.

otherwise you can use find and replace to modify your formulas or....

The solution I would go with would be to put all info for all years onto a
single worksheet (think database) and use formulas to derive values for the
current year. At that point you have no need to add a new worsheet each year.
--
HTH...

Jim Thomlinson


"Maarkr" wrote:

I added a summary worksheet at the end of the sheets to summarize some info.
I will name the range and use it to pull in info from an Access db. I want
to link this to the FIRST worksheet only. What happens is every fiscal year
they add a new worksheet in the first position, so this year ws1 is 'MD
2010', but in a few months they will add 'md 2011', and I want my summary to
link to the worksheet in the first position, not any particular name. (so
the cell link ='md 2010'!B2 won't work) thx

Link to the FIRST worksheet
 

the reason for the way I described it is because I'm dealing with a lot of
'one-dimensional' people who are afraid to do anything but their simple
spreadsheet in case something goes wrong. Otherwise I'd write a quick Access
db to handle the data for them... I write dbs and build links to files for a
large org, so I need a way thru vb or reference to look at the first
worksheet without opening it so I don't have to modify a hundred worksheet
links on the first of every fiscal year.

"Maarkr" wrote:

I added a summary worksheet at the end of the sheets to summarize some info.
I will name the range and use it to pull in info from an Access db. I want
to link this to the FIRST worksheet only. What happens is every fiscal year
they add a new worksheet in the first position, so this year ws1 is 'MD
2010', but in a few months they will add 'md 2011', and I want my summary to
link to the worksheet in the first position, not any particular name. (so
the cell link ='md 2010'!B2 won't work) thx