Excel help Needed

[454697819]

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

[custard]

need an example of how your data is structured.

[454697819]

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?

[Sir Trev]

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!

[454697819]

yes.. but I have managed to make it work using a different method, its working "better" this way I think

[custard]

i am intrigued, how did you get it to work?

[454697819]

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..

[Sir Trev]

Numbers, I'll send you a PM

[beabert]

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..

Thats how i would of done it, didnt speak up incase i was off the mark lol.

[454697819]

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