Dynamic worksheet reference

[paul28]

How does one reference a worksheet name in a formula so that it is
dynamic rather than static? What I would like to be able to do is to
have the user of the spreadsheet be able to enter a department name in
a cell on a summary tab and have the spreadsheet automagically grab
the data from that department's data worksheet.

My office uses Excel 2000. I have tried using the indirect function
but I get into trouble because it appears that the indirect function
makes the data reference static. I cannot copy my formula down the
column and have the references change appropriately.

My array formula is this bear:

=IF(ISERROR(AVERAGE(IF(ISNUMBER('2-West Data'!$B6:$AA6)*(MONTH('2-West
Data'!$B$2:$AA$2)=(MONTH($B$5))),('2-West Data'!$B6:$AA6)))),"N/
A",AVERAGE(IF(ISNUMBER('2-West Data'!$B6:$AA6)*(MONTH('2-West Data'!$B
$2:$AA$2)=(MONTH($B$5))),('2-West Data'!$B6:$AA6))))

It actually works quite well for what I want. It checks the
department's data tab (in this case 2-West data) for weekly data that
corresponds to the month entered in cell b5 of the summary sheet. It
then averages the data that exists and returns the result to the
summary sheet.

I would appreciate any and all help in being able to make the sheet
reference dynamic. It would be nicer to have one summary sheet that
can be used for any department versus creating a summary sheet for
every department.

 

[T. Valko]

So you want these rows to increment as you drag copy down:

'2-West Data'!$B6:$AA6

Biff

 

[paul28]

So you want these rows to increment as you drag copy down:

'2-West Data'!$B6:$AA6

Biff

Exactly.

 

[T. Valko]

Exactly.

As you can see, it's quite a mess:

A1 = some sheet name

=IF(ISERROR(AVERAGE(IF((ISNUMBER(INDIRECT("'"&A$1&"'!B"&ROWS($1:6)&":AA"&ROWS($1:6))))*(MONTH(INDIRECT("'"&A$1&"'!B2:AA2"))=MONTH($B$5)),INDIRECT("'"&A$1&"'!B"&ROWS($1:6)&":AA"&ROWS($1:6))))),"N/A",AVERAGE(IF((ISNUMBER(INDIRECT("'"&A$1&"'!B"&ROWS($1:6)&":AA"&ROWS($1:6))))*(MONTH(INDIRECT("'"&A$1&"'!B2:AA2"))=MONTH($B$5)),INDIRECT("'"&A$1&"'!B"&ROWS($1:6)&":AA"&ROWS($1:6)))))

Maybe you should reconsider!

Biff

 

[T. Valko]

As you can see, it's quite a mess:

A1 = some sheet name

=IF(ISERROR(AVERAGE(IF((ISNUMBER(INDIRECT("'"&A$1&"'!B"&ROWS($1:6)&":AA"&ROWS($1:6))))*(MONTH(INDIRECT("'"&A$1&"'!B2:AA2"))=MONTH($B$5)),INDIRECT("'"&A$1&"'!B"&ROWS($1:6)&":AA"&ROWS($1:6))))),"N/A",AVERAGE(IF((ISNUMBER(INDIRECT("'"&A$1&"'!B"&ROWS($1:6)&":AA"&ROWS($1:6))))*(MONTH(INDIRECT("'"&A$1&"'!B2:AA2"))=MONTH($B$5)),INDIRECT("'"&A$1&"'!B"&ROWS($1:6)&":AA"&ROWS($1:6)))))

Maybe you should reconsider!

Biff

It wouldn't be too bad if you dumped the error trap and just let any natural
errors happen. Or, dump the error trap and hide the errors with conditional
formatting. Are these results used in any downstream calculations? If the
answer is no I'd dump the error trap. If the answer is yes I still might
dump the error trap depending on what those other calculations are.

Biff

 

[Roger Govier]

Hi Biff

The "beast" could be tamed a little with the use of some named formulae.
Again assuming Sheet name is entered in cell A1

Name Data
Refers to
=INDIRECT("'"&Sheet1!$A$1&"'!B"&ROW(Sheet1!B1)&":AA"&ROW(Sheet1!B1))
Name Months
Refers to =INDIRECT("'"&Sheet1!$A$1&"'!B2:AA2")

The array formula with error trap then becomes

=IF(ISERROR(AVERAGE(IF(ISNUMBER(Data)*MONTH(Months)=MONTH($B$5),Data))),
"N/A",AVERAGE(IF(ISNUMBER(Data)*MONTH(Months)=MONTH($B$5),Data)))

And since the most likely error is DIV/0 on blank data rows, then maybe
=IF(COUNT(Data)<1,"N/A",AVERAGE(IF(ISNUMBER(Data)*MONTH(Months)=MONTH($B$5),Data)))