Seeking advice on which methods to use to build a spreadsheet with reports.

[atdemeo]

Greetings all,

Bit of a lengthy post, be warned!

I am currently working on building on the spreadsheet I use for managing the data in entertainment lighting systems.

I currently know enough about excel and basic formulas and VBA to think I know how it works and then end up getting frustrated because I don't get the full picture. So I'm looking for particular topics I should study in order to do it the right way. All this talk of Pivot tables, power pivot, power view leaves me wondering where to start... So I am going to explain my goals, and ask you guys to push me in the right direction.

Some quick vocab so you guys understand what I am referencing:
Channel - an ID referring to a Lighting fixture. There can me multiple fixtures referred to by one channel.
Fixture - A lighting instrument. There are many different types.
Universe - A control group, 1 - 256.
DMX Address: Similar to that of an IP address, 1-512.
Position: Location of Fixture. There are multiple fixtures per position.
Circuit: Power source for fixture. There can be multiple Fixtures per circuit.

I have attached a small example that I made, with the reports just made by moving columns and resorting manually. There are several more columns of information to be added.

All of the information changes on a show by show basis.
At the most, there would be 2000 row items in the table.

The workflow is as follows: A different program generates a CSV file with some, but not all of the information in the "Source Table." I would need excel to populate what it can with information from the CSV, and leave the rest blank. I would then manually edit the blank cells, and would need to merge the changes back into the CSV, in the same format, in order to update the changes in the external program. This process can occur several times throughout a show.

Once the final edits to the SourceTable are generated, I'm looking to build various reports that are linked to the "Source Table" that will populate automatically, sorted the correct way, including some but not all columns, and linked to the correct information. The idea is to edit only one sheet, and then Batch print the rest without having to touch them. The example includes only a few of several different reports that would be generated. There would also be several sheets of labels, as well as a sheet that calculates the total weight per position, the total number of fixtures by type per position, total number of fixtures per universe, total fixtures and power draw per circuit, etc etc.


Some cell values need to match and reference values in other sheets. For example, The Fixture Type column would ideally be an auto-fill sort of thing so I could pick from a list as I started typing the name. This would reference an external sheet or workbook that has information relating to that fixture - the weight, power draw, dimensions, # of control channels needed, etc. and import that information into the table.


What do you guys think is the right approach? Right now, its pretty much a basic range with various formulas pulling information here and there, and then I manually create the reports. I know tables is a step in the right direction. What should I start reading up on? Pivot tables? Power view? VLookups? VBA?

Thanks for your help!

[CAntosh]

Based on my understanding of what you've described above, it sounds like your needs mostly revolve around rearranging and manipulating the data in your source table. Designing your reports first, then figuring out how to autopopulate as much of the info as you can seems to be the goal. To that end, I would get comfortable working with the formulas necessary to import your data, so that the data in your reports will mostly autopopulate. VLOOKUP is useful in that regard, but I would recommend learning the INDEX-MATCH substitute for VLOOKUP instead. INDEX-MATCH is more versatile (it can 'go left' where VLOOKUP can't) and it's faster on larger datasets. You'll have no trouble finding tutorials on that particular skill. You might want to play around with pivot tables as well to get a feel for how they can be used to display only chosen parts of your data - the efficacy of pivot tables will depend on the nature of each report.

It doesn't sound like you'll need anything that absolutely requires VBA, but VBA might become a necessity if your workbook becomes so stuffed with formulas that it runs too slowly. I would design your spreadsheet without VBA, then explore VBA as a possibility if you need to combat workbook bloat or if you run into something that simply can't be done efficiently another way.