Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link from worksheet in one workbook to worksheet in another workbo | Excel Discussion (Misc queries) | |||
Link worksheet totals to a summary worksheet in the same workbook | Excel Worksheet Functions | |||
I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
How can I link cell colours from worksheet to worksheet/workbook? | Excel Worksheet Functions | |||
How to link Excel worksheet tab names to dates in each worksheet? | Excel Worksheet Functions |