| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| worksheet to worksheet . . . | Excel Worksheet Functions | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Sum up columns in different sheet with error check | Excel Discussion (Misc queries) | |||
| Change sheet reference in new workbook | Excel Discussion (Misc queries) | |||
| Indirect reference to another sheet that can be dragged | Excel Worksheet Functions | |||
Linear Mode
