I want my spreadsheet to follow this sort of flow:
1. Enter the name of an HVAC unit in cell A3.
2. Enter the number of rooms the unit serves in cell B3.
3. Designate a general classification (e.g. Hospital, Prison, Education) for each room in C3,C4...C*, etc. from a pulldown menu.
4. Designate a specific classification (e.g. Operating room, autopsy room, dining hall, auditorium) for each room in D3-D*, etc. from a conditional pulldown based on the selection in 3.
5. Cells in E3,F3,G3,H3 - E*,F*,G*,H* will populate with data based on the selection in 4.
6. Cell I3 contains a formula that caculates a value based on the numbers in cells E3 - H3. Cell I4 calculates a value for E4 - H4, etc.
7. Cell J3 sums all of the values in column I that correspond to the unit name designated in A3.
8. Cell K3 contains a pulldown of values.
9. Cell L3 calculates a final value based on the numbers in J3 and K3.
I've attached the spreadsheet, since my description is probably confusing. I've already implemented the pulldowns menus. Basically, I want the sheet to automatically merge the appropriate number of cells in columns A, B, J, K and L based on the response in cell B3.
For example, if TEST is the name of the HVAC unit, and it serves 2 rooms, I want A3 and A4 to merge into one cell that displays "TEST". I also want B3 and B4 to merge and display "2". I want J3 and J4 to merge and display a number. I want K3 and K4 to merge and retain the pulldown functionality. Finally, I want L3 and L4 to merge. All of this based on the response of "2" in cell B3.
I feel like this might be beyond the capabilities of excel, but does anyone have any suggestions on how I could do this?
Bookmarks