ExcelBanter - indirect funtion

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.

Pete Elbert wrote:

A couple of days ago I asked this question and the reply I received I did not
under stand. The response was create new lists for each customer and use the
indirect function and this would solve my dilema. I hate to be so thick
headed on this, but I do not understand this function. Could an example be
shown to help me? Here is the question again:
D7 has a dropdown list with customer names (10 -12 customers). F16 thru F38
each have a dropdown list with a vlookup of a products list. Infomation in
columns H thru L are dependent on the product that is shown in cells F16 thru
F38 for unit size, price and extended price. The dilema is that all of the
customers do not have the same pricing. How can I make the cells in F-16
thru F-38 reflect the product and all the associated pricing info for which
ever customer I select from the dropdown list? Can it be done? Pete

--

Dave Peterson