MULTY WorkBOOKS - No Macro's

[Guest]

I am still seeking a solution:

I am trying to create 1 Workbook that contains a summary of data from 100 other Workbooks. From each of the 100 Workbooks, I would like to take the data from 3 cells. And on my summary workbook/sheet, have Excel automatically pull that data from all 100 workbooks/sheets, add the 3 cells, and give me a combined grand total.

I can link 1 one external workbook/sheet and it's 3 cells into the summary workbook/sheet. BUT when I try to add a 2nd workbook/sheet into that same cell, I get a value error.

And I would like for the summary workbook/sheet to automatically update itself when it opens.

Any ideas on the formula to get this to work? And is there a limit to the # of Workbooks that I can pull from?

 

[Ken Wright]

Given that you don't want macros, I would think it unlikely you will ever get
this done in a single cell, simply because of all the filepaths you will have.
I would build a separate sheet that has one cell for each cell you want to link
to in each book, build your links, and then do the maths from that sheet.

You might want to consider using a macro to help you build that sheet though,
even though you won't need the macro once it's done. Each cell will need to
contain a full filepath link to its counterpart cell in the closed workbook, and
300 of those is a lot of typing.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I am still seeking a solution:

I am trying to create 1 Workbook that contains a summary of data from 100

other Workbooks. From each of the 100 Workbooks, I would like to take the data
from 3 cells. And on my summary workbook/sheet, have Excel automatically pull
that data from all 100 workbooks/sheets, add the 3 cells, and give me a combined
grand total.

I can link 1 one external workbook/sheet and it's 3 cells into the summary

workbook/sheet. BUT when I try to add a 2nd workbook/sheet into that same cell,
I get a value error.

And I would like for the summary workbook/sheet to automatically update itself when it opens.

Any ideas on the formula to get this to work? And is there a limit to the # of

Workbooks that I can pull from?

 

[Tushar Mehta]

It would help if you were to share (a) the formulas you are using for
the workbook that works and the one that doesn't, and (b) what's
different between the work book names / sheet names / sheet contents.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions