Excel 2007 : automatically finding data based on multiple independent variables

**Cashew** · 12-27-2010, 11:48 PM

Hello all,

I'm not sure exactly how to phrase my question in excel language. This makes it difficult to browse the forum to find answers. I've attached a file with the chart and with a template of what I'd like it to look like. If anyone has any ideas how to do this and/or what topics I should be looking up I'd appreciate the help.

I have data in a chart. There are six variables, all independant of each other, that when combined make one particular type of window. This window has a specific value for RSI and SHGC. I'm trying to automate the chart searching process.

Cheers!

**NBVC** · 12-28-2010, 12:00 AM

Based on your sample file, try:

in B11:

Please Login or Register  to view this content.

in B12:

Please Login or Register  to view this content.

then format these cells as: Custom: 0;-0;;@

this will make 0's appear as blank cells.

Note: In case you are unsure of how to make drop downs, you will need to name each column of items on Sheet1 via the Formulas|Define Name feature.

Then in Sheet2, in each input cell, you go to Data|Validation.. select List and enter =yourlistname where yourlistname is the name of the corresponding list you want to show.

**Cashew** · 12-28-2010, 01:05 AM

Hey, thanks! Your suggestion works. Thanks also for the details on making drop down menus, they are new to me.

I have another question if I may, is there a way to make the drop down menu list each option only once? Can the drop down menu only have one each of triple/double/single rather than the double/double/double/however many doubles there are in the chart/triple/triple/etc.?

Also, would you mind explaining the formula you used? I can see it referencing the previous sheet, each column of the data, etc. What does the B:B bit mean?

Thanks!

**NBVC** · 12-28-2010, 02:04 PM

List all the unique values for each input column separately somewhere and use those as the named ranges to reference in your data validations.

Have a read up on SUMIFS in Excel help file to better understand it. B:B simply references all of column B. You can also use defined range like B1:B100 if desired.

**Cashew** · 12-29-2010, 01:02 PM

Nice, thanks! Everything works beautifully now.

I wondered whether it would still be able to reference the data cells if I listed the drop down options as a separate abbridged list. I think I understand now; it looks for the defined terms in the range specified.

Hadn't thought to use sum functions of any sort with text. Interesting.

Cheers!