How would you do it? (setting up a table)

[dourpil]

Hi all!

I'm wondering what the most effective set up would be for my data. I'm not really stuck or anything but there are probably tweaks I'm not aware of that would help me.

So basically, I'm setting up a table where daily data will be entered.

As it is now, the file contains several spreadsheets (too many in my opinion) which are all set up more or less identically:

Col. A: Date
Col. B: Result type A (for item #1)
Col. C: Result type B (for item #1)
Col. D: Result type A (for item #2)
Col. E: Result type B (for item #2)
.
.
.
On one of the spreadsheet, this goes up to col. BK (!), meaning you do a lot of navigation through the sheets and file to get to the cell you want to enter your data.
The sheets are all pre-filled: the Date column has all dates from jan 1 to dec 31 and the rest of the sheet is filled little by little.
The data entered will be used for analysis (monthly, quarterly, type A and/or B, ...)


I'm reworking this file and had 2 ideas on how to proceed:

The first one was to create a file with only 1 data-entry spreadsheet with a table containing:
Col. A: Date
Col. B: type (with a data validation list)
Col. C: Result type A
Col. D: Result type B

This would be simple and straightforward. I could use a table and would have very easy analysis formulas.
However, the big downside is that data entry will be extremely slow. Although navigating through a file is kind of a waste of time, it'd still be much faster than having to enter the Type every time.

2nd idea was not really an idea because I'd just copy the current set-up. I'd have wanted to use tables (for data analysis) but tables won't let me use the same header name more than once. Since I want to keep everything condensed, I find it unpleasant to have my columns named "Type A item 1", "Type B item 1", ...
I guess I could not use tables though and work just a little harder on my analysis formulas. But I like a clean sheet where you know where stuff is and where stuff should go.

Any suggestion welcome

Have a great day

 

[Luke M]

I would propose having 4 columns.
Date | Item | Type | Result

Based on what you said, Item can change to various things. Might make this a Data Validation field. Type can only be A or B, could again go with Data Validation. Result will be all the numbers (assuming it's numbers). This setup will give you the most flexibility in data analysis for building your formulas, or using a PivotTable. The other benefit is that user never has to "navigate" to a specific point, they can just fill in their info in newest row.

As you mentioned it, using XL's table structures would be a good idea. Again, that will benefit you later in analysis as you can use structures references rather than having to build a dynamic named range, or change static formulas.

 

[dourpil]

Hi Luke and thanks for your input!

Indeed it would make more sense to go with data validation for Type aswell.

I'd say it's coming down to ergonomy here. The data is moved from paper to the Excel file. With the Item numbers (by the way these are the worst "6-456", "6-457", "HP-1053-WX" names possible) arranged identically on the paper and on the spreadsheet, one can easily navigate with the arrows to enter Type A and Type B results (which are small numbers) in the corresponding fields.
The mouse requires constant attention which makes it laborious to go through memorizing item number and type results, finding them back on the list and still enter the results with the keyboard.

All that in my opinion of course!

Using Forms could be useful here although unpractical aswell since it's an Excel option. A few persons will be working with it on a few computers. It should be enabled in every session of every computer so a bit meh..

thanks again though I'll think about it some more tomorrow!