sales reps pricing

[Esradekan]

obscure header i know, but all i know how to describe it.

i have a worksheet that lists the products i sell.with different
pricing scales on it,,,it is in the form i guess of an array, and i
need to access it by having data (the correct data) put into a sheet.

Now, i have a sheet for each rep, it says on that sheet wether he will
be price1, price2, prioce 3, price 4, price5. It quotes that on cell
g2. I have all tghe products listed, B2:B260, with price 1, price 2
etc, along C1:G1, with corresponding prices in relevant cell.

How can i access the right data, if say i put book 1 in a cell on the
worksheet, and get the corresponding and correct price, in the next
cell, (assume the one containing the formula) for that product??, is
it Index/Match?, or VLookup, and what formula woudl i use?

TIA

Esra

 

[Roger Govier]

Hi

On your master price list sheet, Insert>name>Define>
Name prices Refers to =$B$2:$G$260

On the sheet for each Rep, if their Price to use is entered in cell A1 as 1,
2 , 3 ,4, or 5
the formula required in cell B2 will be
=VLOOKUP(A2,prices,$A$1+1,0)
copy down the sheet as required.

 

[Esradekan]

Hi

On your master price list sheet, Insert>name>Define>
Name prices Refers to =$B$2:$G$260

On the sheet for each Rep, if their Price to use is entered in cell A1 as 1,
2 , 3 ,4, or 5
the formula required in cell B2 will be
=VLOOKUP(A2,prices,$A$1+1,0)
copy down the sheet as required.

--
Regards
Roger Govier









- Show quoted text -

I think i have written this wrong or something, coz thats not what i
want.
Let me try to simplify.
On 'prices' sheet, i have the followingB2:G260)
Price A Price B Price C Price D etc
1 Book1 11.20 11.45 11.70 11.95
2 Book2 12.50 12.75 12.90 13.10
3 Book3 9.95 10.25 11.25 12.25
4 Book4
5 Book5
6 Book6
7 Book7
8 Book8
9 Book9 etc

Each Rep sheet
John Brown Price: Price A

Description Price
A1 A2

I enter the description (A1), and it gives the price in A2.

Does that make sense?

Hope so,
Esra

 

[Max]

One way, illustrated in this quick sample:
http://www.flypicture.com/download/NDgxNzQ=
Sales rep pricing.xls

In the rep's sheet,

Assume the rep's "price" will be input in B1, eg: Price A
with the descriptions listed in A3 down, eg: Book1, Book2, etc

Put in B3, copy down:
=OFFSET(Prices!$A$1,MATCH($A3,Prices!$A:$A,0)-1,MATCH(B$1,Prices!$1:$1,0)-1)

 

[Roger Govier]

Hi Esra

Other than the fact that you have now changed to calling the price
categories A, B etc instead of 1,2 and where on the reps sheet his price
structure is entered, it should work.

The description - Book1, would be in cell A2, and the corresponding price
for that book would be derived from the formula placed in cell B2

If you want to use a Letter A or B etc in cell A1, then change formula to
=VLOOKUP(A2,prices,CODE($A$1)-63,0)
If you want to put the Reps price category in another cell, e.g. D1, and
have your Book list start at A1 rather than A2, then

=VLOOKUP(A1,prices,CODE($D$1)-63,0)

 

[Esradekan]

One way, illustrated in this quick sample:http://www.flypicture.com/download/NDgxNzQ=
Sales rep pricing.xls

In the rep's sheet,

Assume the rep's "price" will be input in B1, eg: Price A
with the descriptions listed in A3 down, eg: Book1, Book2, etc

Put in B3, copy down:
=OFFSET(Prices!$A$1,MATCH($A3,Prices!$A:$A,0)-1,MATCH(B$1,Prices!$1:$1,0)-1­)
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---









- Show quoted text -

Genius, this works fine, thank you so much.
And thanks to all those who helped.

Esra

 

[Max]

welcome, Esra.