+ Reply to Thread
Results 1 to 10 of 10

drop down list help required.

  1. 03-27-2013, 09:13 AM #1
    chardsmith is offline
    Registered User
    Join Date
    09-04-2010
    Location
    Italy
    MS-Off Ver
    Excel 2003
    Posts
    22

    drop down list help required.

    Hi

    Could someone help me out with the following problem

    Here is the file.. I just need to know how to change the second drop down menu named "Position", with a limited output dependent of the selection made in the first drop down list "LRU Description".. example if a AMCU (Item from the first drop down) was selected there would be position 1-3 on the second drop down list, if it was an EADB, it would be 1-7. and then the info in columns F,G and H would be displayed if the required fields..

    I havent had a reply to a previous post, so i hope someone can spare the time to point me in the right direction on where to find the info on the forum or the web...

    Regards

    Richard
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-27-2013, 09:57 AM #2
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: drop down list help required.

    Use this Data Validation setting for cell E306:

    Allow: List
    Source: =OFFSET($E$315, MATCH($D$306, $D$316:$D$365, 0), , COUNTIF($D$316:$D$365, $D$306), )


    If you're unfamiliar with how the OFFSET() function is used to create a range by adjusting a starting position, read the help files in Excel (press F1).
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 03-27-2013, 06:21 PM #3
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: drop down list help required.

    I have added a bit to the above. Hope this helps.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  4. 04-13-2013, 08:17 AM #4
    chardsmith is offline
    Registered User
    Join Date
    09-04-2010
    Location
    Italy
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: drop down list help required.

    Hi Guys

    Thank you both for the help... Sorry for the delay in replying, i have been off ill with dizzy, headache stuff anyway i'm on the mend... I had a look at what you did and think it makes sense... I can see how you did it, i was wondering how can i make the second drop down list default to a blank... So if an item doesn't have a number it will allow it to populate the rest of the information required to complete the table...
    Reply With QuoteRegister To Reply
  5. 04-13-2013, 09:49 AM #5
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: drop down list help required.

    In the main listing, D315:H366, Insert a blank row above each of the LRU Descriptions and their associated columns. Copy the LRU description into the blank row. Leave the POSITION column blank. Fill in the other data for Item Area, Part Number and AMM Reference.

    Now if the Item Description is filled in but a BLANK is selected for the POSITION the rest of the data entered for that item will be filled in appropriately under Area, Part Number, and AMM reference if such data exists.
    Reply With QuoteRegister To Reply
  6. 04-14-2013, 02:23 AM #6
    chardsmith is offline
    Registered User
    Join Date
    09-04-2010
    Location
    Italy
    MS-Off Ver
    Excel 2003
    Posts
    22

    Smile Re: drop down list help required.

    Hi newdoverman

    I tried adding a blank row for each lru, and left the first slot in the position as blank, but it doesnt seem to be working... For example the AMCU.. I now have four rows... all full of data, and the position column shows blank, 1,2,3. but it only displays blank 1,2 and doesnt populate the other entries.... I would also like the position column (E306) to automatically blank out when a new selection is made for the LRU description..(D306)

    Firstly if you can point me in the right direction i would be very grateful.. I will continie to play and see if i can work it out, but i have no idea how to attempt my second issue of making the position column select a blank on the new selection of the first D306

    Lets see if i can work it out before you guys wake up!!
    Reply With QuoteRegister To Reply
  7. 04-16-2013, 09:17 AM #7
    chardsmith is offline
    Registered User
    Join Date
    09-04-2010
    Location
    Italy
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: drop down list help required.

    Hi Guys

    I have submitted my excel spreadsheet for you attention... It works as it is, but i cannot find a way to make the second dropdown list clear the previous selection... Can anyone help me, and also it doesnt seem to work after row 363... I.e wall monitor 9EG is the last option it sees... If you can point me to any reasons why i would be grateful

    Kind Regards
    Attached Files Attached Files
    Last edited by chardsmith; 04-16-2013 at 09:19 AM. Reason: forgot the attachment
    Reply With QuoteRegister To Reply
  8. 04-16-2013, 10:35 AM #8
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: drop down list help required.

    Attachment 228459

    I found that formulae that should have been ARRAY formulae (entered with Ctrl+Shift) were entered as standard formulae. I found too that the ranges in the formulae did not match the actual ranges of data.

    I entered blank rows in the data with the appropriate part data for the drop down lists leaving the other cells blank.

    I found inconsistencies in the data for the first drop down list that didn't match the list of part data. I cleaned up as many as I could find.

    I hope that this helps.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-20-2013, 09:58 AM #9
    chardsmith is offline
    Registered User
    Join Date
    09-04-2010
    Location
    Italy
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: drop down list help required.

    Hi Newdoverman

    Thankyou for finding the errors within the list... I didnt supply the version that you previously suggested i complete, as it didnt work... I can see that you have added blank data fort each position with no data for the other three columns, which is not something i had done previously... I prevously wanted it to default to a blank within the second drop down list to display the other three columns, but think your idea, of forcing a selection to be made will be better...

    Now all i need to find on the forums, is how to make the E306 default to a BLANK cell, after any selection is made in D306..

    I cannot thankyou enough for your help, and again im sorry for the delayed response...
    Reply With QuoteRegister To Reply
  10. 04-20-2013, 10:31 AM #10
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: drop down list help required.

    There may be a way to do what you want using VBA which I don't know.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1