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:
http://contextures.com/xlDataVal01.html#Name

Then in $c$1 of that custTable sheet:
=if(input!a2="","",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="",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.