Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help! making a worksheet more automated?
The main area of my worksheet looks like the following (hopefully, my
formating carries through): Today Week Month Year Actual | Budget | Actual | Budget | Actual | Budget | Actual | Budget My raw data is placed in the far right of the worksheet and looks like: Budget for Each Day | Day1Actual | Day2Actual | Day3Actual ...... The "Actual" part of the worksheet are the day's results. For the Week, Month, and Year, the result is the accumulation of all the daily results up to that point. So, for example, on the 10th week and 6th day of the year, the Year's Actual numbers would be the sum of the previous 9 weeks plus the 6 days, while the Week's numbers would just be the sum of the past 6 days. At the moment, all of the fomulae in the main section of my worksheet must be changed daily in order to pull the raw data from the right. Most of the time, it is simply adding another columns data to the sum results. However, for the beginning of each week and month, I need to start the data range all over again. This is a huge worksheet with a lot of data in it. Is there any way to make this procedure simplier? Is there anyway to have a fomula that says, "This is the 245th day in the year, the 18th day in the month, and the 4th day of the week. Now go pull the appropriate data from the appropriate columns, do the summation of that data, and put it Week:Actual column, the Month:Actual column, and the Year:Actual column." If so, that would be great! But, honestly, if anyone out there has some suggestions with this worksheet, it would be really apprieciated. I would love to make it as automated as possible, but any shortcuts or time-saving steps would be lovely. Thank you for reading! |
#2
|
|||
|
|||
Sorry not to address your actual question, but you might want to condiser
using Microsoft Access to store your data instead. You can then use SQL to do the summation and other related tasks. Excel also offers a feature to import data (e.g. summation) from Access to generate a report, if needed. HTH -- arunkhemlai "redb" wrote: The main area of my worksheet looks like the following (hopefully, my formating carries through): Today Week Month Year Actual | Budget | Actual | Budget | Actual | Budget | Actual | Budget My raw data is placed in the far right of the worksheet and looks like: Budget for Each Day | Day1Actual | Day2Actual | Day3Actual ...... The "Actual" part of the worksheet are the day's results. For the Week, Month, and Year, the result is the accumulation of all the daily results up to that point. So, for example, on the 10th week and 6th day of the year, the Year's Actual numbers would be the sum of the previous 9 weeks plus the 6 days, while the Week's numbers would just be the sum of the past 6 days. At the moment, all of the fomulae in the main section of my worksheet must be changed daily in order to pull the raw data from the right. Most of the time, it is simply adding another columns data to the sum results. However, for the beginning of each week and month, I need to start the data range all over again. This is a huge worksheet with a lot of data in it. Is there any way to make this procedure simplier? Is there anyway to have a fomula that says, "This is the 245th day in the year, the 18th day in the month, and the 4th day of the week. Now go pull the appropriate data from the appropriate columns, do the summation of that data, and put it Week:Actual column, the Month:Actual column, and the Year:Actual column." If so, that would be great! But, honestly, if anyone out there has some suggestions with this worksheet, it would be really apprieciated. I would love to make it as automated as possible, but any shortcuts or time-saving steps would be lovely. Thank you for reading! |
#3
|
|||
|
|||
You can use additional worksheet pages for "actual data" input instead of to
the right of your main area. For example you could create 12 new worksheets, one for each month. Set each worksheet up with days, week totals, month totals, whatever sums you need to pull to your main area. If you group these worksheets you can do all the set up at once for these sheets. Name them after you ungroup, and then update your main area to pull the various totals. You can also experiment with "at-if" formulas in your main area to summarize your actual data; however, you will still have to set up identified daily input columns for all days prior to their need. Hope this helps. "redb" wrote: The main area of my worksheet looks like the following (hopefully, my formating carries through): Today Week Month Year Actual | Budget | Actual | Budget | Actual | Budget | Actual | Budget My raw data is placed in the far right of the worksheet and looks like: Budget for Each Day | Day1Actual | Day2Actual | Day3Actual ...... The "Actual" part of the worksheet are the day's results. For the Week, Month, and Year, the result is the accumulation of all the daily results up to that point. So, for example, on the 10th week and 6th day of the year, the Year's Actual numbers would be the sum of the previous 9 weeks plus the 6 days, while the Week's numbers would just be the sum of the past 6 days. At the moment, all of the fomulae in the main section of my worksheet must be changed daily in order to pull the raw data from the right. Most of the time, it is simply adding another columns data to the sum results. However, for the beginning of each week and month, I need to start the data range all over again. This is a huge worksheet with a lot of data in it. Is there any way to make this procedure simplier? Is there anyway to have a fomula that says, "This is the 245th day in the year, the 18th day in the month, and the 4th day of the week. Now go pull the appropriate data from the appropriate columns, do the summation of that data, and put it Week:Actual column, the Month:Actual column, and the Year:Actual column." If so, that would be great! But, honestly, if anyone out there has some suggestions with this worksheet, it would be really apprieciated. I would love to make it as automated as possible, but any shortcuts or time-saving steps would be lovely. Thank you for reading! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
Making cells compulsary to fill in on a worksheet | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions |