Linking in Excel

**Spatcher** · 01-14-2006, 07:33 PM

Say I need to gather data from 4 workbooks and average that data and place that average into a cell. How would I do this?

And could you give me an example please?

Chevy · 01-15-2006, 03:18 AM

Open all 4 workbooks (I assume you mean 4 separate xls files)

In the one you want to show the results in (let's assume column A will have the averages) Go to A1, type in =AVERAGE( , then click on the cells in each workbook you want to use in the calculation. Once you've clicked on the last, go back to the one you're editing, add a ) to the end of the cell you're working on, and press enter.

**Spatcher** · 01-15-2006, 11:57 AM

Chevy: your method didn't work

Here is what I have, but it doesn't work if I add more workbooks, and yeah, more .xls's.

=AVERAGE('C:\Documents and Settings\<user name>\My Documents\[Week1.xls]Week''s Average'!B2, '[Week2.xls]Week''s Average'!B2)

If I add in this after the last B2: ,'[Week3.xls]Week"s Average'!B2)
I will get an error saying there is an invalid reference to an external link, even though it is open right next to Week2.xls

**Spatcher** · 01-15-2006, 12:02 PM

Never mind Chevy, there is a reason that you're the Manager of M$ Computing

I left the error on the function bar, and opened up Week3 and it worked.

Beaucoup de Thanks Chevy!!!

Chevy · 01-15-2006, 03:06 PM

Glad to help ....

01-14-2006, 07:33 PM	#1 (permalink)
Spatcher Henry Join Date: Apr 2005 Posts: 3,484 OS: Mac OSX	Linking in Excel Say I need to gather data from 4 workbooks and average that data and place that average into a cell. How would I do this? And could you give me an example please? __________________ JohnThePilot: Cenedl heb iaith, cenedl heb galon (a nation without a language is a nation without a heart) John Butler Trio: Go take a step outside, see what's shakin' in the real world - Good Excuse Paul Kelly: Just add a dollop of tomato sauce for sweetness and that extra tang - How to Make Gravy

01-15-2006, 11:57 AM	#3 (permalink)
Spatcher Henry Join Date: Apr 2005 Posts: 3,484 OS: Mac OSX	Chevy: your method didn't work Here is what I have, but it doesn't work if I add more workbooks, and yeah, more .xls's. =AVERAGE('C:\Documents and Settings\<user name>\My Documents\[Week1.xls]Week''s Average'!B2, '[Week2.xls]Week''s Average'!B2) If I add in this after the last B2: ,'[Week3.xls]Week"s Average'!B2) I will get an error saying there is an invalid reference to an external link, even though it is open right next to Week2.xls __________________ JohnThePilot: Cenedl heb iaith, cenedl heb galon (a nation without a language is a nation without a heart) John Butler Trio: Go take a step outside, see what's shakin' in the real world - Good Excuse Paul Kelly: Just add a dollop of tomato sauce for sweetness and that extra tang - How to Make Gravy

01-15-2006, 12:02 PM	#4 (permalink)
Spatcher Henry Join Date: Apr 2005 Posts: 3,484 OS: Mac OSX	Never mind Chevy, there is a reason that you're the Manager of M$ Computing I left the error on the function bar, and opened up Week3 and it worked. Beaucoup de Thanks Chevy!!! __________________ JohnThePilot: Cenedl heb iaith, cenedl heb galon (a nation without a language is a nation without a heart) John Butler Trio: Go take a step outside, see what's shakin' in the real world - Good Excuse Paul Kelly: Just add a dollop of tomato sauce for sweetness and that extra tang - How to Make Gravy

01-15-2006, 03:18 AM	#2 (permalink)
Chevy Retired Join Date: Jul 2003 Location: Notlob Posts: 5,452 OS: Vista Ultimate My System	Open all 4 workbooks (I assume you mean 4 separate xls files) In the one you want to show the results in (let's assume column A will have the averages) Go to A1, type in =AVERAGE( , then click on the cells in each workbook you want to use in the calculation. Once you've clicked on the last, go back to the one you're editing, add a ) to the end of the cell you're working on, and press enter.

01-15-2006, 03:06 PM	#5 (permalink)
Chevy Retired Join Date: Jul 2003 Location: Notlob Posts: 5,452 OS: Vista Ultimate My System	Glad to help ....