software

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(B9amp;quot;costquot;),2,FALSE)

I have made a quot;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 adviseregards,

NigelI 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)

quot;Nigelquot; gt; wrote in message
...
gt; Hi, why doesn't this work?
gt; i have a sheet called stock with a ( named range ) as follows:
gt;
gt; (suppliers) (woodman) (Cost)
gt; woodman mdf ?12.99
gt; glassman plywood ?6.99
gt; metalman mfc ?15.00
gt;
gt; on my other sheet (B), in cell B9 is a data validation using Suppliers
list.
gt; depending on what is shown determines the validation list in C9. ( in this
gt; case i want to select woodman and select plywood ).
gt; my formula is =VLOOKUP(C9,INDIRECT(B9amp;quot;costquot;),2,FALSE)
gt;
gt; I have made a quot;woodmancost based on all cells for woodman and the cost
gt; column next to it.
gt; on sheet B, the cost as per formula should come out with ?6.99 but instead
i
gt; get #N/A.
gt;
gt; i have used examples from Deborah Dalglesh which, on her example it works
gt; fine. am i missing something or doing something wrong?
gt;
gt; please advise
gt;
gt;
gt; regards,
gt;
gt; Nigel
gt;
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

quot;Bob Phillipsquot; wrote:

gt; I am not quite clear as to your objective. What are you expecting to see in
gt; C9 when you select woodman, another DV list, or an amount of 6.99. If the
gt; former, where will you see the 6.99?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Nigelquot; gt; wrote in message
gt; ...
gt; gt; Hi, why doesn't this work?
gt; gt; i have a sheet called stock with a ( named range ) as follows:
gt; gt;
gt; gt; (suppliers) (woodman) (Cost)
gt; gt; woodman mdf 拢12.99
gt; gt; glassman plywood 拢6.99
gt; gt; metalman mfc 拢15.00
gt; gt;
gt; gt; on my other sheet (B), in cell B9 is a data validation using Suppliers
gt; list.
gt; gt; depending on what is shown determines the validation list in C9. ( in this
gt; gt; case i want to select woodman and select plywood ).
gt; gt; my formula is =VLOOKUP(C9,INDIRECT(B9amp;quot;costquot;),2,FALSE)
gt; gt;
gt; gt; I have made a quot;woodmancost based on all cells for woodman and the cost
gt; gt; column next to it.
gt; gt; on sheet B, the cost as per formula should come out with 拢6.99 but instead
gt; i
gt; gt; get #N/A.
gt; gt;
gt; gt; i have used examples from Deborah Dalglesh which, on her example it works
gt; gt; fine. am i missing something or doing something wrong?
gt; gt;
gt; gt; please advise
gt; gt;
gt; gt;
gt; gt; regards,
gt; gt;
gt; gt; Nigel
gt; gt;
gt;
gt;
gt;

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, quot;Supplierquot;

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

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(A1amp;quot;Costquot;),2,FALSE)

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

Was I close?

Nigel wrote:
gt;
gt; Hi Bob,
gt;
gt; when i select woodman in DV list 1, the second DV list will only show items
gt; relative to woodman. ( i.e. materials specific from this one supplier ). then
gt; when i select the material in DV list 2, it shows the cost for that material.
gt;
gt; regs,
gt;
gt; Nigel
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; I am not quite clear as to your objective. What are you expecting to see in
gt; gt; C9 when you select woodman, another DV list, or an amount of 6.99. If the
gt; gt; former, where will you see the 6.99?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Nigelquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi, why doesn't this work?
gt; gt; gt; i have a sheet called stock with a ( named range ) as follows:
gt; gt; gt;
gt; gt; gt; (suppliers) (woodman) (Cost)
gt; gt; gt; woodman mdf 拢12.99
gt; gt; gt; glassman plywood 拢6.99
gt; gt; gt; metalman mfc 拢15.00
gt; gt; gt;
gt; gt; gt; on my other sheet (B), in cell B9 is a data validation using Suppliers
gt; gt; list.
gt; gt; gt; depending on what is shown determines the validation list in C9. ( in this
gt; gt; gt; case i want to select woodman and select plywood ).
gt; gt; gt; my formula is =VLOOKUP(C9,INDIRECT(B9amp;quot;costquot;),2,FALSE)
gt; gt; gt;
gt; gt; gt; I have made a quot;woodmancost based on all cells for woodman and the cost
gt; gt; gt; column next to it.
gt; gt; gt; on sheet B, the cost as per formula should come out with 拢6.99 but instead
gt; gt; i
gt; gt; gt; get #N/A.
gt; gt; gt;
gt; gt; gt; i have used examples from Deborah Dalglesh which, on her example it works
gt; gt; gt; fine. am i missing something or doing something wrong?
gt; gt; gt;
gt; gt; gt; please advise
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; regards,
gt; gt; gt;
gt; gt; gt; Nigel
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson