Data Validation with Indirect

[Guest]

Hi, why doesn't this work?
i have a sheet called stock with a ( named range ) as follows:

(suppliers) (woodman) (Cost)
woodman mdf £12.99
glassman plywood £6.99
metalman mfc £15.00

on my other sheet (B), in cell B9 is a data validation using Suppliers list.
depending on what is shown determines the validation list in C9. ( in this
case i want to select woodman and select plywood ).
my formula is =VLOOKUP(C9,INDIRECT(B9&"cost"),2,FALSE)

I have made a "woodmancost based on all cells for woodman and the cost
column next to it.
on sheet B, the cost as per formula should come out with £6.99 but instead i
get #N/A.

i have used examples from Deborah Dalglesh which, on her example it works
fine. am i missing something or doing something wrong?

please advise


regards,

Nigel

 

[Bob Phillips]

I am not quite clear as to your objective. What are you expecting to see in
C9 when you select woodman, another DV list, or an amount of 6.99. If the
former, where will you see the 6.99?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob,

when i select woodman in DV list 1, the second DV list will only show items
relative to woodman. ( i.e. materials specific from this one supplier ). then
when i select the material in DV list 2, it shows the cost for that material.


regs,


Nigel

 

[Dave Peterson]

I'm gonna bet it's a problem with the way you defined the range names.

I did this.

I created a new sheet.

I put the supplier choices in column A (A2:A4 in this example)
I named A2:A4, "Supplier"

I put the Woodman's choices in column B (B2:B4)
I named B2:B4, "Woodman"

I put the prices for the woodman's cost in C2:C4
But I named B2:C4 (both columns!) WoodmanCost

Then in my example, I used A1, B1 and C1 of sheet1:

A1 contained the Data|Validation that pointed at directly at Supplier.

A2 contained the data|validation that pointed at =indirect(a1)

A3 contained this formula:
=VLOOKUP(B1,INDIRECT(A1&"Cost"),2,FALSE)

==
So my bet was that it was not naming the pair of columns WoodmanCost.

Was I close?