Variables in Linked Sheets possible?

[david1]

Correcting a post in the wrong place 4/16/04. Reposting.

In Workbook (Days), are daily sheets 1-31, all identically
formatted, in which dozens of cells are linked to another
workbook (Daily Summary) which takes data from each
sheet, one day at a time, and prints it. This means that
the links in Daily Summary must be manually redirected
each day, to the next Day's sheet.

1) Is there a way to make this or something like it
possible? ='[Days]Variable'!$B$6 where Variable
(sheetname) is an input cell? Thus, updating a Sheet
reference in one swell foop! OR

2) Can the Change Source option on the Edit Menu be
directed deeper to the Sheet level?

3) One solution is to point the Daily Summary links to
another sheet in another workbook (Daily Input) which
is recreated daily - i.e Day 1 is copied onto Daily Input,
retrieved with links in Daily Summary, then Day 2 is
copied onto Daily Input, etc. The problem of course,
is this is messy, and prone to errors.

I deal with this daily and have not yet found a solution,
much less something elegant. Any input will be
appreciated. Thanks, davi

 

[Dave Peterson]

Since all the data is in the same workbook, you can use =indirect().

(=Indirect() will work with other workbooks, but they must be open.)

=INDIRECT("'" & A1 & "'!$b$6")
(spaces for readability only)

If A1 contained the name of the worksheet.

Correcting a post in the wrong place 4/16/04. Reposting.

In Workbook (Days), are daily sheets 1-31, all identically
formatted, in which dozens of cells are linked to another
workbook (Daily Summary) which takes data from each
sheet, one day at a time, and prints it. This means that
the links in Daily Summary must be manually redirected
each day, to the next Day's sheet.

1) Is there a way to make this or something like it
possible? ='[Days]Variable'!$B$6 where Variable
(sheetname) is an input cell? Thus, updating a Sheet
reference in one swell foop! OR

2) Can the Change Source option on the Edit Menu be
directed deeper to the Sheet level?

3) One solution is to point the Daily Summary links to
another sheet in another workbook (Daily Input) which
is recreated daily - i.e Day 1 is copied onto Daily Input,
retrieved with links in Daily Summary, then Day 2 is
copied onto Daily Input, etc. The problem of course,
is this is messy, and prone to errors.

I deal with this daily and have not yet found a solution,
much less something elegant. Any input will be
appreciated. Thanks, david

 

[david1]

I had tried =Indirect before, but apparently never got my ' & " correct
I'm on the right track now. Thanks muchly