Excel Summary Sheet

[Ket]

Hello,

I have a number of identical workbooks. Is it possible to create a
summary sheet that has the following information.

A1 = Cell C3 from Wkbk1
A2= Cell B4 from Wkbk1
A3 = Cells A17:B27 from Wkbk 1
A4 = Cells:A28:A34 from wkbk 1

I then need a blank row and the same information from Wkbk 2, Wkbk 3
etc etc. In all there are about 100 separate Workbooks all with
different names.

Are there any VB gurus out there?

Any help greatly appreciated.

TIA

Ket

PS Using Excel and WIndows 2K

 

[Kent]

The short answer is "yes" but it could be complicated.

There is no magic bullet for this here tho'. Are A3 & A4
supposed to be totaled?

Excel Support Technician
www.canhelpyou.com

 

[Ozzie]

Ket -

Its time consuming but you can link to as many
spreadsheets as you want. In you summary spreadsheet, go
to the cell that you want the information to appear. Next
start your formula, then click on window, click on the
wkbk1, then enter a plus, then click on worksheet, click
on wkbk2, then hit enter. The result is the addition of
the two cells from the two different spreadsheets. You
can do any formula this way. Once you have done one
formula, don't forget that you can copy the formula...

Here's a sample:
=+[Book1]Sheet1!A1+[Book2]Sheet1!A1+SUM([Book3]Sheet1!
A1:A5)

If you see dollar signs ($) in your formula, then remove
them before copying.

HTH
(e-mail address removed).

 

[Ket]

Thanks for your comments. A3 & A4 do not need to be totalled. I am
aware of the manual process involved. It was the magic bullet that I
was hoping to find!
Thanks again.

 

[Frank Kabel]

Hi
do you have a list of all your 100 workbook names stored in your
summary workbook. If yes you may try the following
Assumptions:
- lets assume your list of workbook names is stored in cells D1100
(format: 'filename.xls'
- all workbooks reside in the same directory
- you only reference single cells (that is no sums for a range, etc.)

Do the following
- download and install the free add-in morefunx.xll
(http://longre.free.fr/english) -> we use the function INDIRECT.EXT as
I assume not all of your workbooks are openend
- in A1 enter the formula
=INDIRECT.EXT("'" & path_name & "[" & OFFSET(D1,INT((ROW()-1)/5)) &
"]sheetname'!C3")
A2:
=INDIRECT.EXT("'" & path_name & "[" & OFFSET(D1,INT((ROW()-1)/5)) &
"]sheetname'!B4")
A3:
=INDIRECT.EXT("'" & path_name & "[" & OFFSET(D1,INT((ROW()-1)/5)) &
"]sheetname'!A17")
A4:
=INDIRECT.EXT("'" & path_name & "[" & OFFSET(D1,INT((ROW()-1)/5)) &
"]sheetname'!A28")

copy these formulas to A6:A9, etc.

Note: This is probably quite slow if you do this for 100+ workbooks.
Give it a try