Vintage79
Board Regular
- Joined
- May 29, 2007
- Messages
- 187
- Office Version
- 2019
- Platform
- Windows
Hello All,
I was wondering if anyone can figure out the best way to do this...
<p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Fruit</p><br /><br />
Range names have been set as Fruit=A2:A4, Veg=D2:D4, Meat=G2:G4. Drop down menus exist in columns J & K.
So, the user will select from a drop down list in J2 (the options are A6:A8), and then in K2 they will be presented with the appropriate options. So, if the user selects fruit in J2, they will get a drop down in K2 with Apples, Oranges, Bananas.
This is as far as I have managed to get on my own! Now I would like to use some kind of lookup function in L2, so that the price is returned for the item selected in J2. I think this might be possible using LOOKUP and setting it to search the array from A1:H4. But the problem with this is that as the inventory grows, I expect to have duplicate values. So, I am hoping to point the lookup to the range name that has been selected in J2, and then return the price based on that. Is this possible?
Once I find a way to do this, the last part should be simple...I would like to return the code to M2. So, for fruit, this would be FR001.
I really appreciate any help!
I was wondering if anyone can figure out the best way to do this...
| Excel 2010 | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Fruit | FR001 | Veg | VG001 | Meat | MT001 | Type | Name | Price | Code | |||||
| 2 | Apples | £1.20 | Onions | £1.65 | Pork | £3.20 | |||||||||
| 3 | Oranges | £0.95 | Carrots | £1.24 | Beef | £4.80 | |||||||||
| 4 | Bananas | £1.30 | Leeks | £1.00 | Lamb | £5.60 | |||||||||
| 5 | |||||||||||||||
| 6 | Fruit | ||||||||||||||
| 7 | Veg | ||||||||||||||
| 8 | Meat | ||||||||||||||
Sheet1 | |||||||||||||||
Range names have been set as Fruit=A2:A4, Veg=D2:D4, Meat=G2:G4. Drop down menus exist in columns J & K.
So, the user will select from a drop down list in J2 (the options are A6:A8), and then in K2 they will be presented with the appropriate options. So, if the user selects fruit in J2, they will get a drop down in K2 with Apples, Oranges, Bananas.
This is as far as I have managed to get on my own! Now I would like to use some kind of lookup function in L2, so that the price is returned for the item selected in J2. I think this might be possible using LOOKUP and setting it to search the array from A1:H4. But the problem with this is that as the inventory grows, I expect to have duplicate values. So, I am hoping to point the lookup to the range name that has been selected in J2, and then return the price based on that. Is this possible?
Once I find a way to do this, the last part should be simple...I would like to return the code to M2. So, for fruit, this would be FR001.
I really appreciate any help!
Last edited:
