Question for all you clever people....

Let me see if I can explain.....

I have worksheet No 1 (called codes) with a list of numbers 1,2,3 etc though
to 10.
On worksheet I have a list of part number that is around 10,000 lines long.
Each part number have a price ranging from £0 to £2000.

From worksheet 1 I need a formula in worksheet 2 showing that if a price is
between £0 and £29 I need the code number "1" inserted in column A. If a
part has a price between £30 and £199 I need a code of "2"
inserting..........and so on down the 10,000 line spreadsheet.

Please help ;-)

Hope this is clear.


TiA

Mag()()

Set up your table on Worksheet 1 which lists your price bands and the
codes for them, in this format:

=A30.00 1
=A330.00 2
=A3200.00 3

etc up to code 10.

Highlight all the cells in this table and Insert | Name | Define and
give the range a name such as "table". Then enter a formula like the
following in A1 on Worksheet 2:

=3DVLOOKUP(F1,table,2,TRUE)

where F1 is the cell containing the price of the item (adjust to suit).
Copy this formula down to A10000 and you should have what you want.

Hope this helps.

Pete


Mag()() wrote:
> Let me see if I can explain.....
>
> I have worksheet No 1 (called codes) with a list of numbers 1,2,3 etc tho=
ugh
> to 10.
> On worksheet I have a list of part number that is around 10,000 lines lon=
g=2E
> Each part number have a price ranging from =A30 to =A32000.
>
> From worksheet 1 I need a formula in worksheet 2 showing that if a price =
is
> between =A30 and =A329 I need the code number "1" inserted in column A. I=
f a
> part has a price between =A330 and =A3199 I need a code of "2"
> inserting..........and so on down the 10,000 line spreadsheet.
>=20
> Please help ;-)
>=20
> Hope this is clear.
>=20
>=20
> TiA
>=20
> Mag()()

Pete,
Worked a treat.

Thanks

mag()()


"Pete_UK" <[email protected]> wrote in message
news:[email protected]...
Set up your table on Worksheet 1 which lists your price bands and the
codes for them, in this format:

£0.00 1
£30.00 2
£200.00 3

etc up to code 10.

Highlight all the cells in this table and Insert | Name | Define and
give the range a name such as "table". Then enter a formula like the
following in A1 on Worksheet 2:

=VLOOKUP(F1,table,2,TRUE)

where F1 is the cell containing the price of the item (adjust to suit).
Copy this formula down to A10000 and you should have what you want.

Hope this helps.

Pete


Mag()() wrote:
> Let me see if I can explain.....
>
> I have worksheet No 1 (called codes) with a list of numbers 1,2,3 etc
> though
> to 10.
> On worksheet I have a list of part number that is around 10,000 lines
> long.
> Each part number have a price ranging from £0 to £2000.
>
> From worksheet 1 I need a formula in worksheet 2 showing that if a price
> is
> between £0 and £29 I need the code number "1" inserted in column A. If a
> part has a price between £30 and £199 I need a code of "2"
> inserting..........and so on down the 10,000 line spreadsheet.
>
> Please help ;-)
>
> Hope this is clear.
>
>
> TiA
>
> Mag()()