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!

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!
>

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!
>