Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

The most effective way to format this spreadsheet

Hello all,

I'm looking for some advice on how to most effectively present the following information and recorded data in a spreadsheet.

I've been asked to create a spreadsheet where the following information is recorded daily (for an indeterminate number of weeks): Date, Batch quantity, production time, Processors, Productivity % (calculated from the 3 previous values)

The above information is then recorded for the following products:
Product Group1: product a, b, c, d, e, f

Product Group 2: product a, b, c, d, e, f, g,h, i

Product Group 3: product a, b, c, d, e, f, g

In other words, the Date, Batch quantity, production time, Processors, and Productivity % are recorded for 22 products once per day, every day.

I need to understand the best way to efficient record this information and present it.

I am thinking that the first tab can be dedicated to a summary of the average productivity %, perhaps also show the average for each day of the week. The summary tab seems like the easy part...

But what is the best way to position the spreadsheet for data entry? Separate tabs for all 22 products is not going to be practical... Ideally, this should all fit into a single page with the daily values entered vertically (oldest first).

Perhaps I am answering my own question... but your opinion matters:

How about like this...
            product a | Batch quantity | production time | Processors |Productivity % | product b | repeat the columns...

day 1                         22                2h             3           [calc]                    ...
day 2 
day 3
...

Open in new window


My only concern is that doing it this way makes it cumbersome to scroll across 110  (22 x 5)  columns in order to enter each day's data!

Any other ideas?

Thanks so much,
Doug
0
Shawn Connelly
Asked:
Shawn Connelly
  • 6
  • 2
  • 2
  • +1
2 Solutions
 
Rory ArchibaldCommented:
I would suggest 7 columns:
Product group, Product, then your 5 data columns.

This will allow you to filter / pivot / query whatever combination you want.
0
 
Shawn ConnellyTechnical WriterAuthor Commented:
Hello Rory,

Thanks for your reply. I don't understand what you are suggesting. I think that is what I am already doing.

The columns are: product (product group isn't that important), and then the four data columns (not five :)... followed by column 6 which repeats for next product.
0
 
Rory ArchibaldCommented:
No, I'm talking about one table with only 6 columns:
Product, Date, Batch quantity, production time, Processors, and Productivity %
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Glenn RayExcel VBA DeveloperCommented:
Rory is suggesting that you consolidate all your data into one table and add two new columns for "Product Group" and "Product".  Under those two columns you would enter the Group names and Product names repeatedly, as needed for each date recorded.  Like so:
new data table
By doing this, you'll be able to take advantage of Excel Tables and be able to quickly summarize and report this data using PivotTables.  See the attached example workbook.

Regards,
-Glenn
EE-Q28539418.xlsx
0
 
Shawn ConnellyTechnical WriterAuthor Commented:
Thank you for the clarification Rory. And thank you so much for your effort in building an example, Glen. That is really appreciated.

But unless, I have misunderstood your intent, the example provided will make it difficult or impractical to enter new dates (the days will accumulate over many weeks).

Please correct me, if I am misunderstanding.
0
 
Shawn ConnellyTechnical WriterAuthor Commented:
I was also thinking of another idea, although I have no idea how to build this concept.

Perhaps in addition to using your suggested format, I add a single date, data entry tab. On this tab, a single day's data is entered for all the products. This data is then automatically inserted into a third tab where the historical data is stored in the suggested format.

The point of this is that ultimately, I want to make it clean/easy to enter in new data, each day.

Is such a idea even possible in Excel (without add-ons)?

Thanks again for your support.
0
 
Glenn RayExcel VBA DeveloperCommented:
sconnell,

Your primary focus should always be the definition and design of your data.  Once you've properly defined what you want to record and store (including any limits you might impose), then creating reports (output) and data entry (input) are your secondary tasks.

Data saved as an Excel Table, as in the example file, allows you to quickly add new data to the table simply by typing in values in the next available row.  The table range is automatically extended, meaning any PivotTables or formulas referencing the table, fields, and data are also updated.  (Try adding a sample line at the bottom to see what happens).  Additionally, if you have formulas (like the Productivity measure), those will be copied into the new row/record as well.

You can further refine the data entry using Data Validation if you wish.  You'd create new master tables listing the correct Product Group names and individual product names that are valid to use, ensuring that only those values are entered into the Excel Table.  Similarly, you can limit date entry and put checks on the values for batch quantity to ensure that overly large or invalid amounts are not entered by mistake.  By extension - and to answer your question above - you can create data entry forms that simplify the data entry process.  You would need to use some automation using Visual Basic (VBA, macros) to make this work, but no special add-ins are necessary, just some coding.

-Glenn
0
 
Shawn ConnellyTechnical WriterAuthor Commented:
Sorry folks for the slow response.

I'll read over the comments this evening.

Thank you to everyone. I really appreciate your assistance. :)
0
 
Martin LissRetired ProgrammerCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
Shawn ConnellyTechnical WriterAuthor Commented:
Yikes! Sorry for the very slow response!

I'll grade this one now.
0
 
Shawn ConnellyTechnical WriterAuthor Commented:
Thank you all.

I appreciate your effort Glenn... I didn't end up using your spreadsheet but it was most certainly helpful in creating what I eventually required. Your advice was also excellent.

:)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now