How should I design the spreadsheets for 3 dimensional data?

My supervisor asked me to go through a word document. It's a list of all the issues found in a building by the night guard, sorted by month. Things like, lights were left on in this room, or the door was unlocked, etc. Here's a small portion

August 2018
104 – found lit.
30 – found unlocked.
42/43 – Alarm off.
18 – door ajar.
155 – found unlocked.
102 – door by 105 found unlocked.
104 – found unlocked.
107 – house left door found unlocked.

I was thinking about having each room be a sheet in my woorkbook, and each row corresponds to a particular month, and each column is a particular issue found with the room. Here's what I have so far

Is there a way to create a template of this table and just copy it over to the rest of the sheets? Is there a better way to do this? For each issue in the word document, I'll add a value of "TRUE" in the corresponding cell in the table, for that particular room.

At the end, I would want to be able to query the data, to answer questions like "Which rooms had the lights left on in August, 2018?"

2 Answers

I think that what you really need is an Access database. Using Excel is this manner is common but not what a spreadsheet is designed for.

Yes, a db app is potentially a better choice for this kind of use case in many situations, but I wouldn't mind using Excel for it as it's usually the more accessible tool to hand.

You just have to think about what kinds of queries you're going to want to do, and design a single table that can serve those needs.

Here's how I think I'd do it:

Data entry occurs in columns B and C - [Date of Observation] and [Location] respectively. You mark off the problems observed in columns D:I in whatever categories or format you had in mind. Use Ctrl+T to 'tableize' the data (which 1. makes it look nice 2. automatically copies down formats and formulas and 3. turns on filter functionality). I added a quick formula in column A to turn dates into months for easy categorization/filtering:

=TEXT(B2,"mmm yy")

You can then:

1. Use this table itself to filter by month/date/room to answer certain kinds of questions. E.g. "How many times in January was the light left on in location CA 30?" Use the filters:

2. Use this table for data-entry only, and create a pivot table on another sheet with filters/slicers set up to use to answer the same type of questions

3. Use this table for data-entry only, and create an input box on another sheet that feeds a reference to array formulas to show answers to certain simple queries (this would take more effort and may not be worth it if the above pivot seems sufficient)
4. Use this table for data-entry only, and create pre-built formulas for each location on their own dedicated sheets (in other words rather than do data entry on sheets for each location as you suggested, do the data entry in the main sheet but still have sheets for each location with filtered views of the data relevant to that location, if that's your desired end state).