software

Dave Peterson: A couple of days ago you sent a response to me that I have
worked on since then. I am still not sure where I am going wrong. The part
of your response to return the unit price for the chosen customer is showing
a #ref error message. From what I understand you to say that I should have 3
worksheets total. (I took the information you suggested and applied to a
sample worksheet just to see what the proccess was. Here is what your 1st
answer was:How about this...

Create a worksheet with two columns in it.

Column A has the customer name (use that column for the data|validation list)
Column B will have the column that should be used for that customer
And cell $C$1 will hold the column that you chose.

Then have another worksheet that has your product table.

Column A has the product id/part number
column B has the description
column C has the unit cost
column D:X has the unit price for each group of customers
(you may have some customers who share the same price list--or not)

So your first table could look like:

Cust1 2
cust2 3
cust3 2 (shares same price list with cust#1
cust4 4
cust5 2 (another shared list)
cust6 7
.....etc

(For this purpose, I'm calling that sheet: CustTable)Then your second table could look like:

part1 desc1 12.50 37.22 44.22 37.22 88.24
part2 desc2 2.50 7.44 8.00 5.22 18.24
.....etc

(For this purpose, I'm calling that sheet: PartTable)

These sheets would be hidden (to keep them safe from prying eyes???).

Then on your input sheet (called Input), you'd have a cell (say A2) that used
data|validation to return the customer name.

Debra Dalgleish has some notes how to use a named range for this
data|validation
cell:
contextures.com/xlDataVal01.html#Name

Then in $c$1 of that custTable sheet:
=if(input!a2=quot;quot;,quot;quot;,vlookup(input!a2,a:b,2,false))

This will return the column that should be used in the vlookup() to return the
unit price for that part number/customer combination.

And you could use this kind of formula to return the stuff you need:

With the part number in F16, you could return the description:
=vlookup(f16,parttable!a:x,2,false)

The unit cost wouldn't be shared with the customer, but it'll be nice to
have it
on that sheet!

And to return the unit price for the chosen customer:
=if(custtable!$c$1=quot;quot;,9999999,vlookup(f16,parttabl e!a:x,custtable!$c$1,false))

I like 9999999 since it'll flag any missing customer--everyone will know that
something is missing. And it won't mess up any subsequent formulas--like
extended price.

That would just be
=QtyCell * UnitPriceCell
on the input sheet.I think you'll have to post the formulas you used. The names of the sheets that
you used and as lots more details.

One common problem is when you use an =vlookup() formula like:

=vlookup(a1,sheet2!a:c,3,false)

This wants to match the value in A1 to column A of sheet2. If it finds a match,
then the 3rd column in that range will be brought back.

If you didn't include enough columns:

=vlookup(a1,sheet2!a:b,3,false)

(Still trying to retrieve the 3rd column--but since the table is only two
columns wide (a:b), a #ref! error will come back.)

Could it be that you just didn't make that range wide enough?
Pete Elbert wrote:
gt;
gt; Dave Peterson: A couple of days ago you sent a response to me that I have
gt; worked on since then. I am still not sure where I am going wrong. The part
gt; of your response to return the unit price for the chosen customer is showing
gt; a #ref error message. From what I understand you to say that I should have 3
gt; worksheets total. (I took the information you suggested and applied to a
gt; sample worksheet just to see what the proccess was. Here is what your 1st
gt; answer was:How about this...
gt;
gt; Create a worksheet with two columns in it.
gt;
gt; Column A has the customer name (use that column for the data|validation list)
gt; Column B will have the column that should be used for that customer
gt; And cell $C$1 will hold the column that you chose.
gt;
gt; Then have another worksheet that has your product table.
gt;
gt; Column A has the product id/part number
gt; column B has the description
gt; column C has the unit cost
gt; column D:X has the unit price for each group of customers
gt; (you may have some customers who share the same price list--or not)
gt;
gt; So your first table could look like:
gt;
gt; Cust1 2
gt; cust2 3
gt; cust3 2 (shares same price list with cust#1
gt; cust4 4
gt; cust5 2 (another shared list)
gt; cust6 7
gt; ....etc
gt;
gt; (For this purpose, I'm calling that sheet: CustTable)
gt;
gt; Then your second table could look like:
gt;
gt; part1 desc1 12.50 37.22 44.22 37.22 88.24
gt; part2 desc2 2.50 7.44 8.00 5.22 18.24
gt; ....etc
gt;
gt; (For this purpose, I'm calling that sheet: PartTable)
gt;
gt; These sheets would be hidden (to keep them safe from prying eyes???).
gt;
gt; Then on your input sheet (called Input), you'd have a cell (say A2) that used
gt; data|validation to return the customer name.
gt;
gt; Debra Dalgleish has some notes how to use a named range for this
gt; data|validation
gt; cell:
gt; contextures.com/xlDataVal01.html#Name
gt;
gt; Then in $c$1 of that custTable sheet:
gt; =if(input!a2=quot;quot;,quot;quot;,vlookup(input!a2,a:b,2,false))
gt;
gt; This will return the column that should be used in the vlookup() to return the
gt; unit price for that part number/customer combination.
gt;
gt; And you could use this kind of formula to return the stuff you need:
gt;
gt; With the part number in F16, you could return the description:
gt; =vlookup(f16,parttable!a:x,2,false)
gt;
gt; The unit cost wouldn't be shared with the customer, but it'll be nice to
gt; have it
gt; on that sheet!
gt;
gt; And to return the unit price for the chosen customer:
gt; =if(custtable!$c$1=quot;quot;,9999999,vlookup(f16,parttabl e!a:x,custtable!$c$1,false))
gt;
gt; I like 9999999 since it'll flag any missing customer--everyone will know that
gt; something is missing. And it won't mess up any subsequent formulas--like
gt; extended price.
gt;
gt; That would just be
gt; =QtyCell * UnitPriceCell
gt; on the input sheet.

--

Dave Peterson

Would it be possible to email you my program?

quot;Dave Petersonquot; wrote:

gt; I think you'll have to post the formulas you used. The names of the sheets that
gt; you used and as lots more details.
gt;
gt; One common problem is when you use an =vlookup() formula like:
gt;
gt; =vlookup(a1,sheet2!a:c,3,false)
gt;
gt; This wants to match the value in A1 to column A of sheet2. If it finds a match,
gt; then the 3rd column in that range will be brought back.
gt;
gt; If you didn't include enough columns:
gt;
gt; =vlookup(a1,sheet2!a:b,3,false)
gt;
gt; (Still trying to retrieve the 3rd column--but since the table is only two
gt; columns wide (a:b), a #ref! error will come back.)
gt;
gt; Could it be that you just didn't make that range wide enough?
gt;
gt;
gt;
gt; Pete Elbert wrote:
gt; gt;
gt; gt; Dave Peterson: A couple of days ago you sent a response to me that I have
gt; gt; worked on since then. I am still not sure where I am going wrong. The part
gt; gt; of your response to return the unit price for the chosen customer is showing
gt; gt; a #ref error message. From what I understand you to say that I should have 3
gt; gt; worksheets total. (I took the information you suggested and applied to a
gt; gt; sample worksheet just to see what the proccess was. Here is what your 1st
gt; gt; answer was:How about this...
gt; gt;
gt; gt; Create a worksheet with two columns in it.
gt; gt;
gt; gt; Column A has the customer name (use that column for the data|validation list)
gt; gt; Column B will have the column that should be used for that customer
gt; gt; And cell $C$1 will hold the column that you chose.
gt; gt;
gt; gt; Then have another worksheet that has your product table.
gt; gt;
gt; gt; Column A has the product id/part number
gt; gt; column B has the description
gt; gt; column C has the unit cost
gt; gt; column D:X has the unit price for each group of customers
gt; gt; (you may have some customers who share the same price list--or not)
gt; gt;
gt; gt; So your first table could look like:
gt; gt;
gt; gt; Cust1 2
gt; gt; cust2 3
gt; gt; cust3 2 (shares same price list with cust#1
gt; gt; cust4 4
gt; gt; cust5 2 (another shared list)
gt; gt; cust6 7
gt; gt; ....etc
gt; gt;
gt; gt; (For this purpose, I'm calling that sheet: CustTable)
gt; gt;
gt; gt; Then your second table could look like:
gt; gt;
gt; gt; part1 desc1 12.50 37.22 44.22 37.22 88.24
gt; gt; part2 desc2 2.50 7.44 8.00 5.22 18.24
gt; gt; ....etc
gt; gt;
gt; gt; (For this purpose, I'm calling that sheet: PartTable)
gt; gt;
gt; gt; These sheets would be hidden (to keep them safe from prying eyes???).
gt; gt;
gt; gt; Then on your input sheet (called Input), you'd have a cell (say A2) that used
gt; gt; data|validation to return the customer name.
gt; gt;
gt; gt; Debra Dalgleish has some notes how to use a named range for this
gt; gt; data|validation
gt; gt; cell:
gt; gt; contextures.com/xlDataVal01.html#Name
gt; gt;
gt; gt; Then in $c$1 of that custTable sheet:
gt; gt; =if(input!a2=quot;quot;,quot;quot;,vlookup(input!a2,a:b,2,false))
gt; gt;
gt; gt; This will return the column that should be used in the vlookup() to return the
gt; gt; unit price for that part number/customer combination.
gt; gt;
gt; gt; And you could use this kind of formula to return the stuff you need:
gt; gt;
gt; gt; With the part number in F16, you could return the description:
gt; gt; =vlookup(f16,parttable!a:x,2,false)
gt; gt;
gt; gt; The unit cost wouldn't be shared with the customer, but it'll be nice to
gt; gt; have it
gt; gt; on that sheet!
gt; gt;
gt; gt; And to return the unit price for the chosen customer:
gt; gt; =if(custtable!$c$1=quot;quot;,9999999,vlookup(f16,parttabl e!a:x,custtable!$c$1,false))
gt; gt;
gt; gt; I like 9999999 since it'll flag any missing customer--everyone will know that
gt; gt; something is missing. And it won't mess up any subsequent formulas--like
gt; gt; extended price.
gt; gt;
gt; gt; That would just be
gt; gt; =QtyCell * UnitPriceCell
gt; gt; on the input sheet.
gt;
gt; --
gt;
gt; Dave Peterson
gt;

I got this one!

See your latest post.

Biff

quot;Pete Elbertquot; gt; wrote in message
...
gt; Would it be possible to email you my program?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt;gt; I think you'll have to post the formulas you used. The names of the
gt;gt; sheets that
gt;gt; you used and as lots more details.
gt;gt;
gt;gt; One common problem is when you use an =vlookup() formula like:
gt;gt;
gt;gt; =vlookup(a1,sheet2!a:c,3,false)
gt;gt;
gt;gt; This wants to match the value in A1 to column A of sheet2. If it finds a
gt;gt; match,
gt;gt; then the 3rd column in that range will be brought back.
gt;gt;
gt;gt; If you didn't include enough columns:
gt;gt;
gt;gt; =vlookup(a1,sheet2!a:b,3,false)
gt;gt;
gt;gt; (Still trying to retrieve the 3rd column--but since the table is only two
gt;gt; columns wide (a:b), a #ref! error will come back.)
gt;gt;
gt;gt; Could it be that you just didn't make that range wide enough?
gt;gt;
gt;gt;
gt;gt;
gt;gt; Pete Elbert wrote:
gt;gt; gt;
gt;gt; gt; Dave Peterson: A couple of days ago you sent a response to me that I
gt;gt; gt; have
gt;gt; gt; worked on since then. I am still not sure where I am going wrong. The
gt;gt; gt; part
gt;gt; gt; of your response to return the unit price for the chosen customer is
gt;gt; gt; showing
gt;gt; gt; a #ref error message. From what I understand you to say that I should
gt;gt; gt; have 3
gt;gt; gt; worksheets total. (I took the information you suggested and applied to
gt;gt; gt; a
gt;gt; gt; sample worksheet just to see what the proccess was. Here is what your
gt;gt; gt; 1st
gt;gt; gt; answer was:How about this...
gt;gt; gt;
gt;gt; gt; Create a worksheet with two columns in it.
gt;gt; gt;
gt;gt; gt; Column A has the customer name (use that column for the data|validation
gt;gt; gt; list)
gt;gt; gt; Column B will have the column that should be used for that customer
gt;gt; gt; And cell $C$1 will hold the column that you chose.
gt;gt; gt;
gt;gt; gt; Then have another worksheet that has your product table.
gt;gt; gt;
gt;gt; gt; Column A has the product id/part number
gt;gt; gt; column B has the description
gt;gt; gt; column C has the unit cost
gt;gt; gt; column D:X has the unit price for each group of customers
gt;gt; gt; (you may have some customers who share the same price list--or not)
gt;gt; gt;
gt;gt; gt; So your first table could look like:
gt;gt; gt;
gt;gt; gt; Cust1 2
gt;gt; gt; cust2 3
gt;gt; gt; cust3 2 (shares same price list with cust#1
gt;gt; gt; cust4 4
gt;gt; gt; cust5 2 (another shared list)
gt;gt; gt; cust6 7
gt;gt; gt; ....etc
gt;gt; gt;
gt;gt; gt; (For this purpose, I'm calling that sheet: CustTable)
gt;gt; gt;
gt;gt; gt; Then your second table could look like:
gt;gt; gt;
gt;gt; gt; part1 desc1 12.50 37.22 44.22 37.22 88.24
gt;gt; gt; part2 desc2 2.50 7.44 8.00 5.22 18.24
gt;gt; gt; ....etc
gt;gt; gt;
gt;gt; gt; (For this purpose, I'm calling that sheet: PartTable)
gt;gt; gt;
gt;gt; gt; These sheets would be hidden (to keep them safe from prying eyes???).
gt;gt; gt;
gt;gt; gt; Then on your input sheet (called Input), you'd have a cell (say A2)
gt;gt; gt; that used
gt;gt; gt; data|validation to return the customer name.
gt;gt; gt;
gt;gt; gt; Debra Dalgleish has some notes how to use a named range for this
gt;gt; gt; data|validation
gt;gt; gt; cell:
gt;gt; gt; contextures.com/xlDataVal01.html#Name
gt;gt; gt;
gt;gt; gt; Then in $c$1 of that custTable sheet:
gt;gt; gt; =if(input!a2=quot;quot;,quot;quot;,vlookup(input!a2,a:b,2,false))
gt;gt; gt;
gt;gt; gt; This will return the column that should be used in the vlookup() to
gt;gt; gt; return the
gt;gt; gt; unit price for that part number/customer combination.
gt;gt; gt;
gt;gt; gt; And you could use this kind of formula to return the stuff you need:
gt;gt; gt;
gt;gt; gt; With the part number in F16, you could return the description:
gt;gt; gt; =vlookup(f16,parttable!a:x,2,false)
gt;gt; gt;
gt;gt; gt; The unit cost wouldn't be shared with the customer, but it'll be nice
gt;gt; gt; to
gt;gt; gt; have it
gt;gt; gt; on that sheet!
gt;gt; gt;
gt;gt; gt; And to return the unit price for the chosen customer:
gt;gt; gt; =if(custtable!$c$1=quot;quot;,9999999,vlookup(f16,parttabl e!a:x,custtable!$c$1,false))
gt;gt; gt;
gt;gt; gt; I like 9999999 since it'll flag any missing customer--everyone will
gt;gt; gt; know that
gt;gt; gt; something is missing. And it won't mess up any subsequent
gt;gt; gt; formulas--like
gt;gt; gt; extended price.
gt;gt; gt;
gt;gt; gt; That would just be
gt;gt; gt; =QtyCell * UnitPriceCell
gt;gt; gt; on the input sheet.
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Dave Peterson
gt;gt;