How do I use a cell on a different worksheet within the same workbook to get
the name of an external workbook that I use for the name or values of a
series in Source Data for a chart. I want to use a workbook as a template and
just change change one cell instead of all of the references to the external
workbook.

Example
=[Test_App_Data.xls]Ticket_Counts!$B$1 - I want to get the Test_App_Data.xls
from a worksheet in the current workbook

Thanks for your help

If you use a name for a series such as myrng
(if done while on the desired sheet, xl fills in the sheet name for you)
=offset($a$1,0,0,counta($a:$a),1)
then for the series. Notice the !
=Test_App_Data.xls!myrng
--
Don Guillett
SalesAid Software

"Teflon TA" <Teflon wrote in message
...
How do I use a cell on a different worksheet within the same workbook to
get
the name of an external workbook that I use for the name or values of a
series in Source Data for a chart. I want to use a workbook as a template
and
just change change one cell instead of all of the references to the
external
workbook.

Example
=[Test_App_Data.xls]Ticket_Counts!$B$1 - I want to get the
Test_App_Data.xls
from a worksheet in the current workbook

Thanks for your help

Hi,

The problem here is that the function one would like to use won't work with
closed workbooks and that appears to be what you want? That function is
INDIRECT.

There is an alternate but limited way:

0. Name the ranges in the external workbooks. I used the same name in each
- D. You may include the titles or not in the defined ranges. Keeping
these names short is critical.

1. First name the range where you will enter a code to indicate the
spreadsheet you want to use and name that range. I named it P in this
example. The values you enter in P are numbers from 1 up you will see why
later.

2. Go to the Insert Name box and define a name such as T to be as follows:

=CHOOSE(P,'C:\Documents and Settings\Shane\My
Documents\California.xls'!D,'C:\Documents and Settings\Shane\My
Documents\Washington.xls'!D)

In this case the data area in each workbook is named Data. You will do well
to keep workbook names, paths, and range names short because you are limited
to the number of characters in a refers to formula.

3. In the spreadsheet enter the following formula and copy it down and over
for as many columns and rows as is in your data area. Plot your chart from
this area.

=INDEX(T,ROW(A1),COLUMN(A1))

If you want to see the data (and chart) from the first workbook enter 1 into
the P cell, if you want to see the data from the second workbook enter 2 into
the P cell.

--
Thanks,
Shane Devenshire


"Teflon TA" wrote:

How do I use a cell on a different worksheet within the same workbook to get
the name of an external workbook that I use for the name or values of a
series in Source Data for a chart. I want to use a workbook as a template and
just change change one cell instead of all of the references to the external
workbook.

Example
=[Test_App_Data.xls]Ticket_Counts!$B$1 - I want to get the Test_App_Data.xls
from a worksheet in the current workbook

Thanks for your help