SV650.org - SV650 & Gladius 650 Forum



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).
There's also a "U" rating so please respect this. Newbies can also say "hello" here too.

Reply
 
Thread Tools
Old 05-11-10, 10:15 AM   #1
454697819
Guest
 
Posts: n/a
Default Excel help Needed

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
  Reply With Quote
Old 05-11-10, 10:28 AM   #2
custard
Guest
 
Posts: n/a
Default Re: Excel help Needed

need an example of how your data is structured.
  Reply With Quote
Old 05-11-10, 10:31 AM   #3
454697819
Guest
 
Posts: n/a
Default Re: Excel help Needed

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?
  Reply With Quote
Old 05-11-10, 12:33 PM   #4
Sir Trev
Member
Mega Poster
 
Sir Trev's Avatar
 
Join Date: Jun 2005
Location: High Wycombe, where the chair factories used to be
Posts: 1,479
Default Re: Excel help Needed

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.
Sir Trev is offline   Reply With Quote
Old 05-11-10, 12:40 PM   #5
454697819
Guest
 
Posts: n/a
Default Re: Excel help Needed

yes.. but I have managed to make it work using a different method, its working "better" this way I think
  Reply With Quote
Old 05-11-10, 12:49 PM   #6
custard
Guest
 
Posts: n/a
Default Re: Excel help Needed

i am intrigued, how did you get it to work?
  Reply With Quote
Old 05-11-10, 01:45 PM   #7
454697819
Guest
 
Posts: n/a
Default Re: Excel help Needed

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..
  Reply With Quote
Old 05-11-10, 01:54 PM   #8
Sir Trev
Member
Mega Poster
 
Sir Trev's Avatar
 
Join Date: Jun 2005
Location: High Wycombe, where the chair factories used to be
Posts: 1,479
Default Re: Excel help Needed

Numbers, I'll send you a PM
__________________
We are the Goon Squad and we're coming to town, BEEP BEEP!
Sir Trev is offline   Reply With Quote
Old 05-11-10, 02:06 PM   #9
beabert
Guest
 
Posts: n/a
Default Re: Excel help Needed

Quote:
Originally Posted by 454697819 View Post
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.
  Reply With Quote
Old 05-11-10, 02:14 PM   #10
454697819
Guest
 
Posts: n/a
Default Re: Excel help Needed

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
  Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
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


All times are GMT. The time now is 02:38 AM.


Powered by vBulletin® - Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.