Different Number of Workbooks each week

[Wally Steadman]

Greetings all.

I have built a spreadsheet that is linked to multiple spreadsheets and sums
up information from each spreadsheet

Example:

MasterSheet.xls

In Cell A1 on Sheet 1 it sums up Cell A1 From Sheet one of 8 Workbooks
called

User1.xls
User2.xls
User3.xls
User4.xls
down to User8.xls

This works fine and the workbook calculates correctly. But the formula is
long and bulky and I was wondering if there is a way to do the formula so
that if only 6 spreadsheets are in the folder that it will still do the sum
of Cell A1 from Sheet1 of each workbook or if I added a 9th Ulser9.xls file
into the folder that it would calculate that in without me having to go back
in to the spreadsheet and changing a plethora of formulas in many cells.

If I can't do this with a Formula, is there a way to do it with VBA to say
look for any files with User*.xls and sum cell A1 from Sheet 1

Any help would be appreciated.

Wally Steadman

[halim]

Hi Wally,

Why you dont use :
sub values()
for r = 1 to 9
with range("A1")
.FormulaArray = "='e:\[user" & r & ".xls]" & sheets(1).name & "!A1"
.value=.value
end with
next r
end sub

Regards,

halim

Wally Steadman wrote:
> Greetings all.
>
> I have built a spreadsheet that is linked to multiple spreadsheets and sums
> up information from each spreadsheet
>
> Example:
>
> MasterSheet.xls
>
> In Cell A1 on Sheet 1 it sums up Cell A1 From Sheet one of 8 Workbooks
> called
>
> User1.xls
> User2.xls
> User3.xls
> User4.xls
> down to User8.xls
>
> This works fine and the workbook calculates correctly. But the formula is
> long and bulky and I was wondering if there is a way to do the formula so
> that if only 6 spreadsheets are in the folder that it will still do the sum
> of Cell A1 from Sheet1 of each workbook or if I added a 9th Ulser9.xls file
> into the folder that it would calculate that in without me having to go back
> in to the spreadsheet and changing a plethora of formulas in many cells.
>
> If I can't do this with a Formula, is there a way to do it with VBA to say
> look for any files with User*.xls and sum cell A1 from Sheet 1
>
> Any help would be appreciated.
>
> Wally Steadman

[Wally Steadman]

Halim,
thanks for your reply. I have a couple of questions about it.

1. Where to I apply the sub to? The Master Worksheet?

2. I will be doing the same summing for multiple cells but all like cells.
So B1 in the master will be the sum of B1 on all worksheets. I actually
have an Array that is from
A1 to O27 and so each cell in the array is summed to like cells in each
workbook. So Do I need this code to follow a couple FOR loops to have it
sum each cell?

I am tracking with what it is doing, just not sure where to apply and have
some kind of idea as to the FOR loops you have shown.

"halim" <hni@cmwi.co.id> wrote in message
news:1152607824.826502.59100@h48g2000cwc.googlegroups.com...
> Hi Wally,
>
> Why you dont use :
> sub values()
> for r = 1 to 9
> with range("A1")
> .FormulaArray = "='e:\[user" & r & ".xls]" & sheets(1).name & "!A1"
> .value=.value
> end with
> next r
> end sub
>
> Regards,
>
> halim
>
> Wally Steadman wrote:
>> Greetings all.
>>
>> I have built a spreadsheet that is linked to multiple spreadsheets and
>> sums
>> up information from each spreadsheet
>>
>> Example:
>>
>> MasterSheet.xls
>>
>> In Cell A1 on Sheet 1 it sums up Cell A1 From Sheet one of 8 Workbooks
>> called
>>
>> User1.xls
>> User2.xls
>> User3.xls
>> User4.xls
>> down to User8.xls
>>
>> This works fine and the workbook calculates correctly. But the formula
>> is
>> long and bulky and I was wondering if there is a way to do the formula so
>> that if only 6 spreadsheets are in the folder that it will still do the
>> sum
>> of Cell A1 from Sheet1 of each workbook or if I added a 9th Ulser9.xls
>> file
>> into the folder that it would calculate that in without me having to go
>> back
>> in to the spreadsheet and changing a plethora of formulas in many cells.
>>
>> If I can't do this with a Formula, is there a way to do it with VBA to
>> say
>> look for any files with User*.xls and sum cell A1 from Sheet 1
>>
>> Any help would be appreciated.
>>
>> Wally Steadman
>