add same cell from all files in same folder
 

Hi all,

I have a folder that contains Excel files with the following name formats..
0001.xls, 0002.xls, 0003.xls...

In each of these files, the cell G12 on sheet called "impact" contains a
value in GBP.

I want to create a file called total.xls, that will add up cell G12 on the
sheet called "impact" on every excel file between 0001.xls and 9999.xls.

Can I do this, and can i do it without using macros. Our security settings
are very tight where i work and i am not *really* allowed to use macros. If a
macro is the only possible solution, then that will have to do.

Cheers

Richard Varcoe

If you're careful, you can do something like this:

Put this formula in A1:
="$$$$$"&"'C:\My Documents\excel\["&TEXT(ROW(),"0000")&"]impact'!$G$12"
(adjust the path, too)

Drag down as for as many files as you have--don't go too far!!!

These formulas will look like:
$$$$$'C:\My Documents\excel\[0001]impact'!$G$12

Then select the exact number of files you have and
Edit|copy
Edit|paste special|Values

Then convert those text strings to formulas:
Select that range to convert
edit|replace
what: $$$$$
with: =
replace all

I'd try it on one or two first. If you do too many and it's wrong (or the
folder/file doesn't exist), then you'll be dismissing dialogs to find that
file/folder--and you'll get #ref! errors returned.






Richard Varcoe wrote:

Hi all,

I have a folder that contains Excel files with the following name formats..
0001.xls, 0002.xls, 0003.xls...

In each of these files, the cell G12 on sheet called "impact" contains a
value in GBP.

I want to create a file called total.xls, that will add up cell G12 on the
sheet called "impact" on every excel file between 0001.xls and 9999.xls.

Can I do this, and can i do it without using macros. Our security settings
are very tight where i work and i am not *really* allowed to use macros. If a
macro is the only possible solution, then that will have to do.

Cheers

Richard Varcoe

--

Dave Peterson