External Data

[memberlogin]

How could I get Data from a several closed workbooks and take the information from worksheets in those workbooks, and then put the infromation into a new worksheet (a summary sheet) just by putting the workbook names into Cells of a workbook.
I hope this make sense!

 

[DPlank]

Simple enough - open all the spreadsheets you need to reference.

In the one summary sheet select your cell as required.
type your formula as required, and when specifying a cell or cell range, select the required book from the Window Menu, and select the cell/cell range directly.

Excel will allow this and when you close the reference books, you will find that Excel will still hold the values as required. There are a couple of exceptions to this rule, such as the

 Countif

function, which returns a #VALUE! error if you update it with the reference workbooks closed.

You can get round this by using nested SUM(IF(xxxxxx)) formulae (if this is an issue).

HTH
David

 

[memberlogin]

at the start of the Query I did ask for closed workbooks!
as I know how to referance data from one worksheet, or workbook myself. I am trying to get simple VBA macro to enable me to Help out a friend who has Data in various sources.

 

[xlbo]

The only way to get data from a closed workbook is via either Data>External data and choose "Excel" as the source (you'll need a named range for this or use an old xl4 macro - example on John Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip82.htm

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244