That's an interesting project. Your spreadsheet would feature at least 3 fields I think - weight, function and motive type. Weight - Light, Medium, Heavy. Function - Strike, Recon, Pursuit, Battle, Assault, Command, Fire, Fire Support. Motive type - Mech, Armour, Hover, Foot Infantry, etc. Maybe a 4th field, Faction - Liao, Steiner, Generic, etc.
I think you have the wrong concept of what I was trying for...
First,this is a set of spreadsheets... You have to pick the one that matches the formation of the unit you are working with (Command company/no command company, battalion lances/no battalion lances, recon/lightning company/no extra company... etc)
Next, you immediately have to save it to another name. Why? Because once you start playing around with it, you inevitably over-write something you pretty much can't come back from.
Now enter the name of the unit in the proper place
Pick the affiliation: Davion, Kurita, Steiner, Marik, Liao or (basically) Other... The reason I say "basically" is that regardless which Periphery Faction, Comstar, Pirate, Merc or whatever you pick, it all defaults to the same thing..
See, part of the problem is that Excel, while it gives you some ability to program, isn't a program environment. What I mean is, that each cell can only hold so much, so you have to limit what you do... In this case, I'm using about 6 nested loops, each referring to a different part of a VLookup call. This gives me enough room to do 6 different factions, but just barely. The idea of then having the "Lance Type" change the default generated lance would be far more code then a single cell can handle. So you have to minimize the code and do a bunch of stuff manually... But that is OK.
The idea behind the original spreadsheet was to be able to open the correct unit organization, select the Affiliation, and then set the BattleForce lance code for each lance, using the NAIS Atlases as a guide. This would allow someone to quickly generate the default lances from those references for play... Then it grew..
Anyway... Once you've picked the BattleForce Lance Code for each lance from the drop-down menu, you have a basic Regiment... The Lance Codes give you affiliation correct Mechs, along with the code definition (I'm not home so I can't get a real one, but say Lance Code L2 means a lance "80-100 Tons"), and the total weight of the lance...
Now we reach the point it is customizable... If you click in the cell that has a Mech's name in it, a drop-box will appear that allows you to select any approved Mech (currently they are all setup for my own alternate universe - The Caz-A-Verse". But they can be easily reset back to the main-stream universe or another AU.). This allows you to change the Mechs to customize the lance or have it fulfill a particular mission. And when you change a Mech, the Lance Tonnage automatically updates so you know that you are still within the parameters of the Lance Code.
Once you've got your lance constructed, you then select the Lance Type from a drop-down list, which really just acts as a reminder of why you put those four Mechs together (because in 6 months, you may not remember that you grouped them because they were all Snipers, or Fast or whatever the reason)...
At the point you have all the lances setup, you can print it if you want and you have a unit TO&E...
But as a painter, I took it a little further...
There is a checkbox on each row, if you check the box, that lance will show through to the other sheets in the Workbook..
There is a sheet that tidies up the first page, getting rid of anything extra (like the lance code and definition)... There is another that allows someone to enter pilot's name and a tiny amount of info... There is another that breaks everything down by weight and gives you averages for lance, company, battalion and regiment... And still another, that if sorted correctly, will give you a list of the minis and quantity you need to purchase to build the unit...
All of this stuff shows through the moment you check the box next to a lance... And if you only want to build a company, you just don't check the lances with default Mechs and then use just the other sheets for information. Only the basics like Unit name and Company nickname will show through, so that keeps all the default stuff from showing when you don't plan to keep it (at least on every sheet but the first one)...
Anyway, that's the penny tour of how it all works.
Caz