Yeah!!! Its working.

I got to looking at my drop down list for the 1-11, 12-24, 25-99 and so on
and realized that 100-249 wasn't showing up. It was because I hadn't
captured that cell while making my list.

Thank you sooo very much!

I love this site!

"BigPig" wrote:

One more thing,

Another thing I thought might be:

What cell did you select as your reference for 'offset'? Assuming that
everything else is sorted, then it may be that your 'reference' cell needs to
be adjusted to a different cell. The reference cell as in: 'A1', see below.
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A4),MATCH(B1, Sheet2!B1:F1))

Where cell 'A1' is a reference point from where 'offset' searches x amount
of columns 'match(b1,sheet2!b1:f1)', and rows 'match(a1, sheet2!a2:a4)', from
the reference cell 'sheet2!A1'.

"Misty" wrote:

Hi - Its me...again

I got the formula to work, but its not pulling the right amounts for each
type of wood. For example: if I click on A. Mah and choose 1-11, its not
giving me the correct price. How would you suggest I trouble shoot that?

Thanks.
Misty

"BigPig" wrote:

Hi Misty,

The example I provided earlier was all on the same spreadsheet. So when you
are using data validation, index and match, in a different spreadsheet as you
have found out it doens't work.

Here's what you can do:
You can use the formula 'offset' along with named cell ranges and data
validation.
It sounds a lot more complicated than it is.

1. Put you data in sheet 2. Ie column a, a1=WoodTypes, a2=A Mah, a3=Cherry,
a4=Oak

2. Highlight the cells b1 to f1, right click, format cells, text.

3. Cells b1 to f1, 100-249, 1-11, 12-24, 250+, 25-99
The reason for the mix, is they are actually 'sorted' in ascending order

4. Populate the rest of the cells with the corresponding prices accordingly.
In the example I used, I only used A Mah through Cherry, and 100-249 to
25-99, and used your price range and some other prices that seemed right.

5. In Sheet1 select cell A1, go to Insert, name, define, type in WoodTypes,
in the refers to field select the little table on the right, select the
sheet2 tab, and select the cell range a2 through a4 in my example, in yours I
am sure it's longer. Click on the little box to the right of the define
name/refers to field, click add, and close.

6. With a1 still selected go to data validation, allow, list, and in the
field 'source' type in =WoodTypes. Now cell a1 should have a little triangle
next to it.

7. Repeat the process for the €˜number of sheets starting with cell b1 in
sheet1, don't put any spaces in your defined name. Meaning, use No_Of_Sheets,
in lieu of No. Of Sheets. With Naming cell ranges, you cant have any spaces.
Likewise, when using Data validation, you cant refer to cells outside of the
active sheet, unless they are €˜named.

8. Now in cell c1 of sheet1 type in:
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A4),MATCH(B1, Sheet2!B1:F1))

What this formula essentially does is selects a cell x amount of rows, and x
amount of columns from the reference, in this case cell A1 in sheet2.

I picked this method because of the reference to sheets that you were using.
Since excel is math based, it searches through data thats sorted. When you
combine symbols like €“ and +, it will mess up your equation, thats why I
suggested formatting your €˜no of sheets range as text.

To find out more about data validation and named cell ranges, refer to Debra
Dalgleishs site: http://www.contextures.com/excelfiles.html#DataVal

As I mentioned in an earlier post, her website is more than helpful.

Let me know how everything turns out.