Question for all you clever people....

M

Mag\(\)\(\)

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()()
 
P

Pete_UK

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
 
M

Mag\(\)\(\)

Pete,
Worked a treat.

Thanks

mag()()


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
 
Top