In a worksheet called "amex summary.xls" I have several cells that reference
external files to retrieve data from cells within those worksheets. One of
the Summary sheet cells reads as follows:
='[amex charge_constance lundy.xls]summary'!$D$3
It pulls values from one file into a summary file. Within the summary file
there are about 110 entries for each individual outside file.

Is there a way to make one cell in the summary worksheet reference the
outside file name and then have the 70 entries for that sheet look to that
cell for the filename and concatenate the filename with a particular cell
reference?

The reason for this is if a new person needs to be added to the "amex
summary.xls" file I first copy and paste one of the existing cell groups and
then have to go in and edit 110 separate line items so they now reference the
new file name.
Each month employees receive bills from their amex card and submit an
expense sheet electronically. I then summarize all the info via the "amex
summary.xls" file and use the summary data to post to our accounting system
instead of having to go in and read each individual file.

You need the INDIRECT function.
http://www.cpearson.com/excel/indirect.htm
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com



"rfcomm2k" wrote in message
...
In a worksheet called "amex summary.xls" I have several cells that
reference
external files to retrieve data from cells within those worksheets. One of
the Summary sheet cells reads as follows:
='[amex charge_constance lundy.xls]summary'!$D$3
It pulls values from one file into a summary file. Within the summary file
there are about 110 entries for each individual outside file.

Is there a way to make one cell in the summary worksheet reference the
outside file name and then have the 70 entries for that sheet look to that
cell for the filename and concatenate the filename with a particular cell
reference?

The reason for this is if a new person needs to be added to the "amex
summary.xls" file I first copy and paste one of the existing cell groups
and
then have to go in and edit 110 separate line items so they now reference
the
new file name.
Each month employees receive bills from their amex card and submit an
expense sheet electronically. I then summarize all the info via the "amex
summary.xls" file and use the summary data to post to our accounting
system
instead of having to go in and read each individual file.

1. Thank you Anne, that may help. However, I may not have described my
problem as precisely as needed. Currently there is a file called "amex charge
summary.xls.

2. In this file there are several references to other files, one of which
reads "='C:\Documents and Settings\rfuller\My Documents\pmorris\amex
charges\[amex charge_william bynum.xls]summary'!$C$5".

3. There is a cell in each section (D2, D41, D80, D119, etc) that goes to
the external file and retrieves the name of the person reporting expenses.
Then there are several lines in each section that reference the summarized
data from the external files. I want to take the filename found in (D2, D41,
D80, D119, etc) and concatenate that into the formulas as represented in
paragraph 2 above. This way, if I need to add another section I need only to
copy an existing section and then change only one formula instead of changing
ALL the formulas in the new section.

I will be happy to email a sample of the summary file and one of the data
files if it would help. Just tell me where to send it.


"Anne Troy" wrote:

You need the INDIRECT function.
http://www.cpearson.com/excel/indirect.htm
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com



"rfcomm2k" wrote in message
...
In a worksheet called "amex summary.xls" I have several cells that
reference
external files to retrieve data from cells within those worksheets. One of
the Summary sheet cells reads as follows:
='[amex charge_constance lundy.xls]summary'!$D$3
It pulls values from one file into a summary file. Within the summary file
there are about 110 entries for each individual outside file.

Is there a way to make one cell in the summary worksheet reference the
outside file name and then have the 70 entries for that sheet look to that
cell for the filename and concatenate the filename with a particular cell
reference?

The reason for this is if a new person needs to be added to the "amex
summary.xls" file I first copy and paste one of the existing cell groups
and
then have to go in and edit 110 separate line items so they now reference
the
new file name.
Each month employees receive bills from their amex card and submit an
expense sheet electronically. I then summarize all the info via the "amex
summary.xls" file and use the summary data to post to our accounting
system
instead of having to go in and read each individual file.