How do I use an indirect reference to a sheet in another workbook?
 

I am working with a formula which references a sheet in another workbook:

=SUM('Q:\ServCtr\ACCT\[ACCT Validation.xls]AL'!$D$290:$D$318)

This works, however the "AL" above refers to Alabama and I need to use this
formula for each state which has a separate page. I have a list of all state
abbreviations in my current workbook and would simply like to reference this
by cell instead of using a different state abbreviation on each line. I've
attempted many variations of the following formula (where B7 is the cell
which contains the abbreviation AL)...but no luck.

=SUM(INDIRECT('Q:\ServCtr\ACCT\[ACCT Validation.xls]"&B7&"'!$D$290:$D$318))

Any suggestions would be greatly appreciated! Thanks!

How do I use an indirect reference to a sheet in another workbook?
 

Using INDIRECT requires that this file be open otherwise it won't work.
Also, since the file MUST be open you don't need the full path:

=SUM(INDIRECT("'[ACCT Validation.xls]"&B7&"'!D290:D318"))

Biff

"ClintG" wrote in message
...
I am working with a formula which references a sheet in another workbook:

=SUM('Q:\ServCtr\ACCT\[ACCT Validation.xls]AL'!$D$290:$D$318)

This works, however the "AL" above refers to Alabama and I need to use
this
formula for each state which has a separate page. I have a list of all
state
abbreviations in my current workbook and would simply like to reference
this
by cell instead of using a different state abbreviation on each line.
I've
attempted many variations of the following formula (where B7 is the cell
which contains the abbreviation AL)...but no luck.

=SUM(INDIRECT('Q:\ServCtr\ACCT\[ACCT
Validation.xls]"&B7&"'!$D$290:$D$318))

Any suggestions would be greatly appreciated! Thanks!

How do I use an indirect reference to a sheet in another workb
 

Thanks Biff! I appreciate your note & advice.

"Biff" wrote:

Using INDIRECT requires that this file be open otherwise it won't work.
Also, since the file MUST be open you don't need the full path:

=SUM(INDIRECT("'[ACCT Validation.xls]"&B7&"'!D290:D318"))

Biff

"ClintG" wrote in message
...
I am working with a formula which references a sheet in another workbook:

=SUM('Q:\ServCtr\ACCT\[ACCT Validation.xls]AL'!$D$290:$D$318)

This works, however the "AL" above refers to Alabama and I need to use
this
formula for each state which has a separate page. I have a list of all
state
abbreviations in my current workbook and would simply like to reference
this
by cell instead of using a different state abbreviation on each line.
I've
attempted many variations of the following formula (where B7 is the cell
which contains the abbreviation AL)...but no luck.

=SUM(INDIRECT('Q:\ServCtr\ACCT\[ACCT
Validation.xls]"&B7&"'!$D$290:$D$318))

Any suggestions would be greatly appreciated! Thanks!

How do I use an indirect reference to a sheet in another workb
 

Most people are disappointed when they find out that the other file needs to
be open.

Biff

"ClintG" wrote in message
...
Thanks Biff! I appreciate your note & advice.

"Biff" wrote:

Using INDIRECT requires that this file be open otherwise it won't work.
Also, since the file MUST be open you don't need the full path:

=SUM(INDIRECT("'[ACCT Validation.xls]"&B7&"'!D290:D318"))

Biff

"ClintG" wrote in message
...
I am working with a formula which references a sheet in another
workbook:

=SUM('Q:\ServCtr\ACCT\[ACCT Validation.xls]AL'!$D$290:$D$318)

This works, however the "AL" above refers to Alabama and I need to use
this
formula for each state which has a separate page. I have a list of all
state
abbreviations in my current workbook and would simply like to reference
this
by cell instead of using a different state abbreviation on each line.
I've
attempted many variations of the following formula (where B7 is the
cell
which contains the abbreviation AL)...but no luck.

=SUM(INDIRECT('Q:\ServCtr\ACCT\[ACCT
Validation.xls]"&B7&"'!$D$290:$D$318))

Any suggestions would be greatly appreciated! Thanks!