Book with 365 sheets??

[Robert Crandal]

I thought I would create a workbook that contains 365 sheets
(one sheet for each day of the week) that will contain rows
of daily numerical data.

However, if my workbook has 365 tabs, wouldn't that seem
a bit too clunky or ineffecient???

Does anybody have any design ideas for a single workbook
that would contain/operate on 365 sheets which could cover
an entire year period???

Thank you!

 

[Barb Reinhardt]

What exactly do you hope to gain by having 365 sheets that a one worksheet
(or maybe 12) couldn't accomplish with some pivot tables?

 

[BotRot]

Hello Robert, please see inline

| I thought I would create a workbook that contains 365 sheets
| (one sheet for each day of the week) that will contain rows
| of daily numerical data.


How many rows? If the number of rows are arbitrary, would you exceed 20 rows?
1000 rows?
Are you able to re-arrange the row-column structure of your data to reduce the
rows?


| However, if my workbook has 365 tabs, wouldn't that seem
| a bit too clunky or ineffecient???

Maybe. Could say yes, if the sheets were just about full of daily data
bit hard with Excel 2007 (even with previous versions of excel too), you could say
well it's the "only" way, given that you still wanted to only use Excel.

Otherwise, if there is still a lot of space (as in cells) on a sheet left blank,
as in your
daily data only used a small amount (or percentage) of a worksheet then, 365
sheets
per workbook maybe inefficient.

It also depends, if the workbook data is picked up from another system or process,
that reads daily data on a per worksheet basis, then again it's the best for the
situation.

That can be a hard question Robert.

| Does anybody have any design ideas for a single workbook
| that would contain/operate on 365 sheets which could cover
| an entire year period???

To respond on "face value" for your question, yes. Having more than one day, or
as many days
of data that will easily fit on one Worksheet, and if the data need to be
referenced, you can encapsulate
the daily data within a named range.

- or -

Set your columns, and fill down the rows with one column representing date.

- or -

Use Access?

I worked for one company that just placed one day of data, on one Worksheet, in
one Workbook, and saw
that it takes many workbooks make up a year, but it worked (well) for their
purposes.

| Thank you!

Hope I assisted you even a little.

Regards,
- BotRot.

 

[Robert Crandal]

How many rows? If the number of rows are arbitrary, would you exceed 20
rows?
1000 rows?
Are you able to re-arrange the row-column structure of your data to reduce
the
rows?

Each day will contain anywhere from 1 to 25 business transactions. Data for
each
transaction will be saved in its own unique row (which has 13 columns).

To respond on "face value" for your question, yes. Having more than one
day, or
as many days
of data that will easily fit on one Worksheet, and if the data need to be
referenced, you can encapsulate
the daily data within a named range.

- or -

Set your columns, and fill down the rows with one column representing
date.

- or -

Use Access?

I worked for one company that just placed one day of data, on one
Worksheet, in
one Workbook, and saw
that it takes many workbooks make up a year, but it worked (well) for
their
purposes.

Right now we are saving each daily worksheet into its own separate file.
Therefore,
at the end of 1 year, our folder/directory will contain 365 individual Excel
files.
I was just thinking, couldn't we just have ONE master Excel file in our
directory
that is somehow smartly and efficiently compacted into ONE workbook with
365 sheets????

Could I somehow program a drop down list box that allows the user to
select a date??? Once a date is selected, couldn't Excel load the
spreedsheet
for that day???

Thank you!

 

[Robert Crandal]

What exactly do you hope to gain by having 365 sheets that a one worksheet
(or maybe 12) couldn't accomplish with some pivot tables?
--

Hmmm, I skimmed over some material regarding pivot tables and they mostly
seem to be used for analyzing sets of data??? Is that right??


Right now, our company saves daily work transactions into individual
spreadsheet files.
Therefore, at the end of 1 year, our directory will have accumalted 365
separate files,
which is not terribly bad.

However, I just got to thinking....could it be possible to efficiently store
a year's worth of
work in a SINGLE Excel file, smartly and efficiently?? Ideally, I would
like to be able
to create a drop down list box that allows the user to select the current
date. Once the
current date is selected, I want Excel to load the worksheet for that day.
Is something
like this possible?? Do I need to use Visual Basic?? Or is this still
something that
pivot tables could solve??

Thank you for your help Barb!

 

[EricG]

Let's say you average 100 transactions per day for a full year. That would
add up to 100*365 = 36500 transactions. An Excel 2003 worksheet can have up
to 65,536 rows on a single worksheet, so there should be plenty of room to
store a whole year's worth of transactions on one sheet. If you can do that,
you can either use the Autofilter to filter the data by date, type of
transaction, amount, etc (whatever you're interested in), or you could create
a pivot table from the data, which allows you to organize and manipulate data
in many useful ways.

Much better than having 365 files or even 365 tabs in one file.

You could create a macro that would read in all the individual files and
place the data on one worksheet, and then create a pivot table from that
data. A single button is all that you would need, and you could run it every
day if you want.

HTH,

Eric

 

[L. Howard Kittle]

If toast always lands butter-side down, and cats always land on their

Steven Wright (1955 - )

That's called a Cat Motor. It spins just a few inches off the floor.

H'wd