GriffinsPal
New Member
- Joined
- Apr 14, 2014
- Messages
- 40
Hi all-
First, thank you to all who have helped me with various stuck points thus far. I have learned alot but have also realized how much more there is to know.... par for the course I guess.
Hoping one of you wizards will have a few minutes to help me out on my latest sticking point. I think it is pretty straight forward, but I am a little stuck.
I have a workbook that has several worksheets. Each worksheet contained data for an entire year, with each row containing a date and then relevant data associated with that date. Some worksheets contain data that has similar data on another worksheet (Number of trainees for example) other worksheets have data that is only found on that single worksheet (# of people served, for example).
What I want to do is write a macro that will extract certain elements by month(those referenced above as an example) and sum them across the entire worksheet (for those elements that are only found on a single worksheet), or sum them across all worksheets in which they appear, and then copy those sums to a table in a different worksheet ("totals").
If I were just doing this within an excel file I would write a formula in the respective cells within the "totals" worksheet using the SUMIF function, but because this file will be used by many different users, I worry that the formulas could be inadvertently corrupted, so I thought writing a Macro and activating through a command button would be safer would be better
If I were to write it as a single function for a single worksheet that will sum data for January I think it would be as follows:
=SumIf(C3:C150,1,F3:F150) where the first range is the range in which the month data is located, "1" is the month of january, and the second range contains the data elements I want added. (data on all sheets start in row3, but the last row of data will be different in each sheet. I don't expect any sheet to have more than 150 rows of data, but I expect there is a way of detecting the last row of data and including only that much in the ranges referenced above)
How can I avoid writing a new line of code for each month for which I am trying to get a total?
Also, what would I have to do to extend this formula across more than one workbook to, say, add # of trainees recorded on worksheet (WS1)to the # of trainees recorded on another worksheet (WS2) and then assign that sum to a particular cell (let's say B4) in worksheet 10?
Thanks so much, in advance, for your help!
First, thank you to all who have helped me with various stuck points thus far. I have learned alot but have also realized how much more there is to know.... par for the course I guess.
Hoping one of you wizards will have a few minutes to help me out on my latest sticking point. I think it is pretty straight forward, but I am a little stuck.
I have a workbook that has several worksheets. Each worksheet contained data for an entire year, with each row containing a date and then relevant data associated with that date. Some worksheets contain data that has similar data on another worksheet (Number of trainees for example) other worksheets have data that is only found on that single worksheet (# of people served, for example).
What I want to do is write a macro that will extract certain elements by month(those referenced above as an example) and sum them across the entire worksheet (for those elements that are only found on a single worksheet), or sum them across all worksheets in which they appear, and then copy those sums to a table in a different worksheet ("totals").
If I were just doing this within an excel file I would write a formula in the respective cells within the "totals" worksheet using the SUMIF function, but because this file will be used by many different users, I worry that the formulas could be inadvertently corrupted, so I thought writing a Macro and activating through a command button would be safer would be better
If I were to write it as a single function for a single worksheet that will sum data for January I think it would be as follows:
=SumIf(C3:C150,1,F3:F150) where the first range is the range in which the month data is located, "1" is the month of january, and the second range contains the data elements I want added. (data on all sheets start in row3, but the last row of data will be different in each sheet. I don't expect any sheet to have more than 150 rows of data, but I expect there is a way of detecting the last row of data and including only that much in the ranges referenced above)
How can I avoid writing a new line of code for each month for which I am trying to get a total?
Also, what would I have to do to extend this formula across more than one workbook to, say, add # of trainees recorded on worksheet (WS1)to the # of trainees recorded on another worksheet (WS2) and then assign that sum to a particular cell (let's say B4) in worksheet 10?
Thanks so much, in advance, for your help!
