Return multiple row headings with an entry

[solrz]

Hi

I'm trying to put together a price list to help make life easier for myself and I'm finding out little by little how powerful excel can be. However, I've hit a stumbling block.

I have a list where I can select appropriate options and accessories for windows (the one's you look out of not the operating system), and as a summary I'd like to be able to collate a list of the options that have been chosen as there are almost a hundred or so options and accessories and it would be great to check at the end and see a summary of what options each particular item has.

I'm not sure how to make it work. Any help would be much appreciated.
[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]Glass Options[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Frame Options[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Antique Lead[/TD]
[TD]Diamond Lead[/TD]
[TD]Georgian Panes[/TD]
[TD]Silent Glass[/TD]
[TD]Solar Glass[/TD]
[TD]Self Cleaning Glass[/TD]
[TD][/TD]
[TD]White[/TD]
[TD]Black/White[/TD]
[TD]Oak/White[/TD]
[TD]Grey/White[/TD]
[TD]Fire Hinges[/TD]
[TD]Silver Handles[/TD]
[TD]Gold Handles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bedroom[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bathroom[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lounge[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*******[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dining Room[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Wicker]

You might want to make a drop down list for each location and each group of options. To do that:
1. On a new sheet in the xls, make a list (one cell under the other) of the options for a location.
2.Go back to the original sheet and click on the cell where you want the dropdown list to appear.
3.Choose Validation from the Data Menu.
4.Choose List from the Allow option's drop-down list.5.Click the Source control and drag to highlight the cells ex(Sheet2'!A1:A4). Alternately, simply enter the reference (=Sheet2'!$A$1:$A$4). You might want the $ option if you need to reuse the list other places on the sheet.
6.Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won't present a drop-down list.
Click OK.
This version is tedious at first but can save time later on.

Alternatively, you can also (going off of the sample you provided) do something like this at the end of the C row and then just drag down to copy it to the other locations:
=if(B3>0,$B$2," ","")&if(C3>0,$C$2," ","")&if(d3>0,$d$2," ","")&if(e3>0,$e$2," ","")&if(f3>0,$f$2," ","")&if(g3>0,$g$2," ","")&if(h3>0,$h$2," ","")&if(i3>0,$i$2," ","")&if(j3>0,$j$2," ","")&if(k3>0,$k$2," ","")&if(l3>0,$l$2," ","")&if(m3>0,$m$2," ","")&if(n3>0,$n$2," ","")&if(o3>0,$o$2," ","")
The basic jist of this is if(B3>0,$B$2," ","")& where "B3" is checking to see if its value is greater than 0. If it is not or is simply empty, it will display nothing for that portion. If it is, it will display the header of that column with a space and a comma (for example since the first column for Bedroom has a 1, the cell with the formula of just ="if(B3>0,$B$2," ","")&" will display "Antique Lead, ").
The longer version does not seperate each type of option, as I figured things like Hinges are not necessarily Frames and you had added them to show a glimpse of the overall scope of your table.

I wasn't sure how you wanted to address the different numbers you used in your table, whether it was a total number of variations or a number in stock, so I left them out for now.

Please let me know if any of this helps and/or if you need something tweaked/clarified.