write a simple macro for summing
|
Original Message
|
Name: ssmarsha
Date: July 18, 2007 at 06:12:59 Pacific
Subject: write a simple macro for summingOS: xpCPU/Ram: n/aModel/Manufacturer: d600 |
Comment: "I have a sheet ( sheet 2) that has about 8 array formulas that need to be replicated everytime the sheet is reproduced. I am not too familiar with excel so what am asking may be able to be dealt with in a few ways. The array formulas pull information from another sheet (sheet 1) in the same workbook. the data from sheet 1 changes daily. and currently there are macros on sheet 1 that pull the data from the bigger source ( an extremely large company database). What I need is that once the macros pull the information from the Larger database and put it on sheet 1, i need another macro, to run the formulas in to the cells on sheet 2, so that the calculations can be performed. the formulas that i am dealing with are array formulas, and they are most either count (if(if(if))) or sum(if(if(if))). where they sums times that are associated with one criteria in another column and fall in between a range. Example: FRUIT qty apples 5 apples 15 bananas 5 apples 12 oranges 8 bananas 13 bananas 22 count the number of "bananas" that are greater than 4 but less than 20 (answer: 2) sum the qty of "bananas" that are greater than 4 but less than 20 (answer: 18) i need help either writing a macro to perform the operations itself or that knows to insert the array formula in the cells."
Report Offensive Message For Removal
|
|
Response Number 1
|
Name: DerbyDad03
Date: July 18, 2007 at 08:58:38 Pacific
|
Reply: (edit)Please explain how the sheets are "reproduced". If I copy a sheet, the formulae get copied also. Is this not happening in your situation?
 Report Offensive Follow Up For Removal
|
|
Response Number 2
|
Name: ssmarsha
Date: July 18, 2007 at 10:14:54 Pacific
|
Reply: (edit)yes the formula get copied as well,its just that sometimes the people who are required to track information, clear the contents of the cells, and then run a macro that populates information from sheet 1 to sheet 2. I want to add another macro to sheet 2 that will perform the other 8 operations in the 8 cells on sheet 2. so that way in the event that the contents are cleared, the data i need to show up will show up when the macro is ran.
Report Offensive Follow Up For Removal
|
|
Response Number 3
|
Name: DerbyDad03
Date: July 18, 2007 at 10:47:10 Pacific
|
Reply: (edit)Why not expand the range of the cells that get copied form sheet 1 to sheet 2 to include the cells that have the formulae? If they've been cleared from Sheet 2 they'll be replaced, if not, they'll be overwritten.
Report Offensive Follow Up For Removal
|
Use following form to reply to current message:
