Idle Banter For non SV and non bike related chat (and the odd bit of humour - but if any post isn't suitable it'll get deleted real quick).![]() |
![]() |
|
Thread Tools |
![]() |
#1 |
Guest
Posts: n/a
|
![]()
I am trying to do a little project for our MD and I have got so far but cant get the next bit working.
I have a front sheet with drop down lists created from Named ranges of data from the other sheets in the workbook. The drop down lists work fine picking the items from the lists from the other sheets, what I now need is formula where you select your item from the drop down list in the adjacent column it selects the items you need to pick from store, these will be also stored as a named data range on another sheet... can anyone help, ill be more than happy to email the sheet across to play with? I dont think the "if" command applies or the Vlookup but ai may very well be wrong. Cheers Alex |
![]() |
![]() |
#2 |
Guest
Posts: n/a
|
![]()
need an example of how your data is structured.
|
![]() |
![]() |
#3 |
Guest
Posts: n/a
|
![]()
in what respect.. its lists of Plant
Such as Tools in the named range "plant" Radial Arm Cross Cut Saw Hammer Drill 110v Transformer 110v Task Lighting 110v Upright Extension lead 110v Tower Scafold WH 3m I then need once I have selected one of the above for it to put the corresponding pick list next to it... from the following range (incomplete) Radial Arm Cross Cut, Saw Blade, Safety Gaurd Hammer Drill 110v Transformer 110v Task Lighting 110v Upright Extension lead 110v 5m 4 no. 125/150/200mm Castor 4 No. 250mm adjustable Leg does this make sense? |
![]() |
![]() |
#4 |
Member
Mega Poster
Join Date: Jun 2005
Location: High Wycombe, where the chair factories used to be
Posts: 1,479
|
![]()
So you want pick list 2 to be dependant on pick list 1?
If so you need to use the "indirect" function for the second drop down list. Let me try and write the steps: 1 create range1 as a simple single column list with pick list 1 details in it. 2 beside each range1 entry add the name of the dependant lists. Create these separately further over on the worksheet, labelling them all carefully of course. 3 create a new range called range1a that has BOTH columns of the first list 4 on your input page (assuming the above are on a workings tab out of the way) create lookup 1, using range1. (for this example say it's in cell a5) 5 create lookup 2 but instead of using =rangex in the list source field use =indirect(vlookup(lookup,a5,range1a,2,false)) Tips For range1 entries that do not have a sub list use a separate range so you can pick "N/A" or similar in lookup 2. This only works with a clean sheet - if you change lookup 1 it does not change lookup 2. I'm sure it can be done but that bit I'm not sure of!
__________________
We are the Goon Squad and we're coming to town, BEEP BEEP! Last edited by Sir Trev; 05-11-10 at 01:02 PM. |
![]() |
![]() |
![]() |
#5 |
Guest
Posts: n/a
|
![]()
yes.. but I have managed to make it work using a different method, its working "better" this way I think
|
![]() |
![]() |
#6 |
Guest
Posts: n/a
|
![]()
i am intrigued, how did you get it to work?
|
![]() |
![]() |
#7 |
Guest
Posts: n/a
|
![]()
I changed it fully, all the info went into a data base and I am looking the V look up its not what I wanted but it will work and probably be more user friendly come implementation..
|
![]() |
![]() |
#8 |
Member
Mega Poster
Join Date: Jun 2005
Location: High Wycombe, where the chair factories used to be
Posts: 1,479
|
![]()
Numbers, I'll send you a PM
__________________
We are the Goon Squad and we're coming to town, BEEP BEEP! |
![]() |
![]() |
![]() |
#9 |
Guest
Posts: n/a
|
![]() |
![]() |
![]() |
#10 |
Guest
Posts: n/a
|
![]()
they way have done it means one more operation for the end user, ill cope as its a billion times better than what we have.
Thanks all Sir trev ill reply sometime today. Cheers |
![]() |
![]() |
|
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
MCN show at Excel. | jacksuzukisv650 | Bikes - Talk & Issues | 15 | 10-02-10 10:49 AM |
Excel | Graciepants | Idle Banter | 15 | 31-03-09 10:52 PM |
Some basic HELP with EXCEL pls | 600+ | Idle Banter | 4 | 02-10-07 01:59 PM |
Excel Question | Tris | Idle Banter | 4 | 04-04-07 11:42 AM |
Cheapest MS Excel? | Nekkid | Idle Banter | 7 | 22-01-07 03:19 PM |