Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Sep 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have created a matrix in Excel with 112 products listed in column A and the same 112 products listed across Row 1.
    The purpose of the spreadsheet is as follows:-
    During manufacturing there are half a dozen different cleaning procedures which are implemented when changing from one product to another and the selection of that procedure is dependent on which product was manufactured last and which product is to be manufactured next.
    The idea is to select, from the left hand column, the product which was previously manufactured then select, from the top row, the product to be manufactured next. The cell at the junction of the selected column and row then contains a code letter which relates to the cleaning procedure to be adopted.
    The matrix is quite large and cumbersome to read and I want to make it quick and simple for Operators to use.
    Is it possible to create drop down lists (Either in this sheet or another) where an Operator selects the product last manufactured in one cell then the product to be manufactured next in another cell with the resultant code letter then displayed?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    You can use Data | Validation with the list option to create the dropdown lists, and use a combination of INDEX and MATCH to look up the letter for the selected combination of products.

    Let's say the products are listed in A2:A113 and in B1I1, and letters in B2I113 on Sheet1.
    Create a validation dropdown for the previous product in B1 on Sheet2, and a validation dropdown for the next product in B2 on Sheet2.
    The formula to look up the letter is

    =INDEX(Sheet1!B2I113,MATCH(B1,Sheet1!A2:A113,0),MATCH(B2,Sheet1!B1I1,0))

    I have attached a sample workbook with a small table.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Sep 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Brilliant. Totally brilliant. I can't thank you enough.
    Unfortunately, I wasn't able to set up the Data/Validation in sheet 2 because it wouldn't allow me to select lists from sheet 1 but hey this is still a quantum leap for me

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel won't let you refer directly to a range on another sheet in Data | Validation, but you can define a name that refers to the range, using Insert | Name | Define..., then use that name in Data | Validation, by entering =name in the Source box. I used this for cells B1 and B2 on Sheet2 in the workbook that I attached to my previous reply.

  5. #5
    New Lounger
    Join Date
    Sep 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm now in seventh heaven. Sorry, I didn't understand about naming cells but I've got the idea now and it works perfectly.
    You're a genius. (Incidentally, I need to win a million pounds on the lottery. How about a spreadsheet that forecasts the numbers)

    Thanks again

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If I knew how to win the lottery, I probably wouldn't be posting here in Woody's Lounge...

  7. #7
    New Lounger
    Join Date
    Sep 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Although this is not the same subject as that previously, I thought that the query was so similar it would be OK to continue under the same heading.
    Having had such excellent help from HansV on the use of drop down boxes to select data from a separate sheet, I was hoping that someone could tell me whether itís possible to do a similar thing for the attached sheet.
    We have 112 different formulae which we fill into 44 different containers and my spreadsheet (by selecting data from separate sheets) shows the ĎGuide weightí maximum and minimum which the Filling machine operator uses to check the fill level of the finished product.
    The spreadsheet has 334 lines on it and, whilst I know itís simple for most of us to use filters, I want to present the data in a simple format on a separate sheet so that the data canít be changed and itís easier to use in a production environment. Hopefully the attached sheet shows what Iím trying to achieve.
    I trust the attached sheet will show what I would like to achieve.

    [attachment=85877:Guide_Weights.xls]
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This is a much more complicated question - in fact, I would use Microsoft Access for this myself.

    The dropdown lists should display unique entries - I assume that you don't want the first dropdown list to display "Liquid Soap 123" 3 times, "Liquid Soap 456" 2 times etc.
    To extract the unique items at each step dynamically will require using VBA code. This implies that the users will have to enable macros, otherwise the dropdown lists will not be updated automatically.
    Would that be acceptable?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    An easier solution is to protect the worksheet, but to allow users to Use AutoFilter. That way they can't change the data, but they can use the AutoFilter dropdowns to select an item.

  10. #10
    New Lounger
    Join Date
    Sep 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Once again Hans, thanks for the help. I've been talking to the guy in our company who resolves most of the Microsoft Office problems (Who incidentally was blown away by your solution to my first request and intends to use it in other applications) and with his help added to your solution. we've finally created something which is not perfect but everybody thinks it's wonderful.
    He suggested adding an additional column in which the Formula, Size & container are combined into one description and the search is then carried out on this one column.
    With 334 lines itís a fairly big drop down but itís still a great tool.
    Incidentally, Itís going to cost the Company a fair amount of money (already agreed) because the computers on all the machines are only equipped with Excel Viewer and the programme doesnít work on them so weíre buying Excel for all of them.
    Cest La Vie

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MickW' post='797682' date='13-Oct-2009 13:59']Incidentally, Itís going to cost the Company a fair amount of money (already agreed) because the computers on all the machines are only equipped with Excel Viewer and the programme doesnít work on them so weíre buying Excel for all of them.[/quote]
    I'm afraid that's inevitable - the Excel Viewer has limited functionality.

Posting Permissions

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