Edit Excel Spreadsheet with cell formatting, formulas, counting, labeling,

General information for the business: We design sell and install audio video and lighting control systems
Kind of development: Customization of existing program
Num. of modules: 1
Description of every module: Edit existing spreadsheet
Description of requirements/functionality: (fyi, a switch leg consists of all of the light fixtures connected to a light switch or dimmer, so, the 4 can lights covering the general kitchen area that all turn on together would be switch leg 1, and the 3 pendents hanging over your kitchen island that all turn on together would be switch leg 2, etc)


1. Need to format the pages so item descriptions show up at the top of every page when printed so electricians/others can interpret the data easier when there are several pages. I need to see these descriptions from the real time excel view while I'm working in excel so when I'm entering data as I go 7 pages deep I know what data to enter into what column without scrolling to the top.

2. Need to count lighting switch legs as they are added and recount them as a total per project when a switch leg is added or deleted from a room. At the end of the list I need a total switch leg count per entire project

3. Need to generate non dynamic identifier for each switch leg called "load ID" on the spread sheet. That way when switch legs are added and deleted as the project goes labeling that has already happened in the field will not change. Maybe something like "LID #". The # would get assigned by excel and would never repeat. This Id is what electricians would write on each wire and it has to be unique and never repeating so wires don't get labeled with the same name/identifier. Maybe this non dynamic identifier is the same one from the room identifier? The letter would correspond to the room and then the number would be the unique identifier.

4. Need to do basic calculation of quantity of light fixtures times wattage of fixtures for total wattage per light switch leg.

5. Need to do dynamic count of switch legs per room. So a kitchen would have switch legs 1-4 with room count of A1, A2, A3, A4 and then a great room would have switch legs 5-8 with room count of B1, B2, B3, B4. The A,B,C, letter would be the identifier for the room and the # would be counting switch legs in the room. Then output total switch legs/room. These totals would be dynamic just like the total switch leg per project.

6. Add up the items from "Load Switching/Dimming Legend" separated out and give totals by item type, DM, APD, FPD, KPD, AD

7. There is a page 2 with keypad names and counting, not really sure what you could do with this. I need to record name of keypad and then count the # of keypads. I would like this counting to be dynamic as well just like the switch leg. I would also like to have a non dynamic identifier for each keypad so as keypads are added or deleted from the design the keypad ID would stay the same. It would be nice if you could see this on the same page as the switch leg count but I couldn't figure out how to make it all fit on one printed page. I would like to see all the keypads in one section by themselves and not broken up between all of the pages. Could you add in the keypads per room with the switch legs then get an output on another page that list all the keypads together in numerical order.?.?
OS requirements: Windows
Extra notes: