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

Hi

On your master price list sheet, InsertnameDefine
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



"Esradekan" wrote in message
oups.com...
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

On Oct 30, 2:28 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

On your master price list sheet, InsertnameDefine
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

"Esradekan" wrote in message

oups.com...



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- Hide quoted text -

- 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 following:(B2: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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Esradekan" wrote
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 following:(B2: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

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)
--
Regards
Roger Govier



"Esradekan" wrote in message
ups.com...
On Oct 30, 2:28 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

On your master price list sheet, InsertnameDefine
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

"Esradekan" wrote in message

oups.com...



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- Hide quoted text -

- 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 following:(B2: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

On Oct 30, 7:59 pm, "Max" wrote:
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
---
"Esradekan" wrote



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 following:(B2: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- Hide quoted text -

- Show quoted text -

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

Esra

welcome, Esra.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Esradekan" wrote
Genius, this works fine, thank you so much.
And thanks to all those who helped.

Esra