Cell formula

  • Thread starter Thread starter Giri
  • Start date Start date
G

Giri

Hi,

Cell A1 in Sheet1 is a numeric value. This value should be
the sum of all B1 of Sheet1 for all excel files in a
paricular path. Is there any way this can be done?

Your help in this regard is very much appreciated. Thank
You.

Regards,
Giri
 
Giri said:
Hi,

Cell A1 in Sheet1 is a numeric value. This value should be
the sum of all B1 of Sheet1 for all excel files in a
paricular path. Is there any way this can be done?
Click to expand...

Assuming you are working with three sheets, the first named "A", the second
"K", and the last "Z": type the following formula in A1:

=SUM(A:Z!B1)

Regards
 
Hi,

I am sorry. But thats not what i am looking for. Your
formula caters for all sheets within a workbook but i am
talking of all workbooks that exist in a particular path.
This means that if i have 1 workbook named "workbook1",
here i want to set a formula in Sheet1 --> A1 such that
for any excel file that is added C:\temp\ as path, it
should cumulate the values of cell B1 of all those files
in c:\temp\. Is this possible ? Maynot be directly as a
formula. Maybe there is a round-about way by providing the
files names & then the formula picking up those filenames
& then summing up etc... so.. i am looking for some
direction at least for how to proceed if not the solution
itself...

Thanks a lot for your help though....:) but i would
greatly appreicate if you could help me out with the above.

Regards,
Giri
 
Hi Giri

Here are a couple of suggestions:

If you have a list of files available, you could use the Indirect function
to get the contents of B1 in each of those files. That is make a column of
file names in C. Then concatenate with text sheet1!B1 in columnD. Then
=Indirect(D1) in col E. Then sum. (Can use an array summation to reduce the
no. of columns.)



Getting a list of files is a bit more complicated. Try an add-in such as
Tushar Mehta's directory listing.


Best is to write some code in VBA using the DIR function.

HTH

Geoff
 
Cell A1 in Sheet1 is a numeric value. This value should be
the sum of all B1 of Sheet1 for all excel files in a
paricular path. Is there any way this can be done?

Your help in this regard is very much appreciated. Thank
You.
Click to expand...

Data > Consolidate or brute force (putting literal references to each workbook's
Sheet1!B1 cell in some range elsewhere, and summing that range) are your only
practical options.
 
Back
Top