Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different Number of Workbooks each week
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different Number of Workbooks each week
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different Number of Workbooks each week
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" wrote in message ups.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for a date's week number in a month (1st, 2nd, etc.) | Excel Discussion (Misc queries) | |||
Macro to copy cells to rows below | Excel Discussion (Misc queries) | |||
How can I get the monday of a given week number? | Excel Discussion (Misc queries) | |||
evaluate data by week number | Excel Worksheet Functions | |||
Convert week number into calendar month? | Excel Worksheet Functions |