not following your answer

[Guest]

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,parttable!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.

 

[Dave Peterson]

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?

 

[Guest]

Would it be possible to email you my program?

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?

 

[Biff]

I got this one!

See your latest post.

Biff

Would it be possible to email you my program?