LINSEY,
Here's a formula that works - I've tested it. It uses the DGET function that is appropriate for your situation IF the records in you data list will always be UNIQUE - in terms of combining the STYLE and VERSION fields.
With the DGET function, you don't require the extra field "STYLEVERSION".
Here are the steps to set up the formula.
1) On your STYLE sheet, assign a range name to all the columns your data occupies (I've used the name "data"
in the example formula).
2) On your separate sheet where you've set up your form, assign a range name to I2 - I've used "style_inp", and assign a range name to I3 - I've used "vers_inp" in the formula below.
3) In an "out of the way" location, and enter this formula:
=AND(STYLE=style_inp,VERSION=vers_inp). When you enter this formula, it will display as: #NAME?. This is normal in this siutation.
4) Assign a range name (I've used the name "crit" - short for "criteria"
to 2 cells - the cell with the formula entered in Step 3, and the cell immediately above it.
5) Optional: For reference I usually enter a label to the left of the range name assigned for the criteria - in this case enter the label "crit" to the left of the 2 cells you've named "crit".
6) Now you're ready to enter the DGET formula. Naturally you can enter it whatever location you prefer.
=DGET(data,"DATA1",crit)
IMPORTANT: For the DGET database function (and other database functions like DSUM, DCOUNTA, etc) to work, they require that the field names in your data list (or "database"
are UNIQUE. In your example, you used DATA, DATA, DATA. I'm sure you meant this to mean different fields of data, and that the ACTUAL field names are different (unique).
In the formula, I've used "DATA1", but you'll need to change this to whatever field you want to pull your data from.
I hope you've been able to follow these steps. If you have any difficulty, let me know. And again, I can always email you the file.
Please appreciate that by using the DGET function, this can "open the door" to a whole new and POWERFUL component of Excel that too many Excel users have overlooked. Excel's database functions are EXTREMELY POWERFUL - in that they can easily accommodate whatever simple or COMPLEX criteria are required in order to isolate the precise data you require for any part of your application.
These "database functions" are related to Excel's "Advanced Filter" - see the menu: Data - Filter - Advanced Filter. The same type of criteria as described for the above DGET function can be used by the Advanced Filter component to either: a) Filter data in place, or b) Extract data (a copy) to a separate sheet. This ability to extract data selectively to a separate sheet, can be VERY useful in generating various type of reports where you want to provide the "detail" to support summary reports. And of course the database functions (DSUM, DCOUNTA, etc), using the same criteria, can be used to provide "Summary" reports - showing just the "totals". The summary data can include whatever type of breakdown you require.
Perhaps I got a "little carried away", but unfortunately there is not very much documentation on this subject, so I feel obliged to help "fill the gap" created by Microsoft.
I hope this helps. (Let me know if you require more.)
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
)