Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Feb 2008
    Location
    Gainsborough
    Posts
    20

    Unanswered: Using Dropdown to select lookup array

    Hi I'm v v new to forums so please be kind!

    I work for a Kitchen retail outfit
    I've been working in excel to create a form for my salesman to use as an order form to eliminate errors on our ordering system

    So far I have listed all the data (Item Code,Description,Cost Price,Sale Price,Retail Price,Offer Price) for every kitchen range that we sell, these are grouped into 5 separate price groups so I put then on their own sheet making 5 sheets named Group 1-5

    I have then made a form that will use vlookup and return the Item code and a full description and all the different prices for that item (Cost,Retail,Sale,Offer)

    I have inserted a dropdown box with all the ranges in it and would like this box to direct the vlookup formula to the correct Sheet for that range.

    I've tried all sorts of different ways to get this and because I have more than 29 ranges I can't use the dropdown that generates a value to use as a lookup which is totally confusing me

    Any help would be gratefully appreciated. I will post a sample of form so far and VLookup formula if needed

    Please note I have no experience of VBA so I might need some extra help if thats the way to sort it

    Kindest Regards
    Andy

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, and welcome to the board. Can you post a sample (not complete, but enough to show what you are attempting) and the result you expect? That will help direct to a solution. My guess is that you will be able to use an INDIRECT function to accomplish what you want.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2008
    Location
    Gainsborough
    Posts
    20
    Help!!!
    How do I attach a mini version of the form to a post?

  4. #4
    Join Date
    Feb 2008
    Location
    Gainsborough
    Posts
    20
    I think I've worked it out

    Here is a mini version..lookups won't work as the data is in a different workbook,but the question is the same... How do I make the Range dropdown list set the lookup array for the pricing formula?
    Attached Files Attached Files
    Novice Excel Dabbler

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, couple of questions. You say there are five Groups. Correct? Each Group will have its own worksheet? You want Cell G2 to be a drop down that lists the Groups? Then, that will refer to the worksheet Group1 or Group2 ... and on each worksheet, there will be data?

    How does the Range List connect to the Item List?

    Can you include a sample of the worksheet that has the data for lookup? It's still hard to see how you want this to work.
    Last edited by shades; 02-05-08 at 14:41.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Feb 2008
    Location
    Gainsborough
    Posts
    20
    Hi again
    Thanks Shades I really appreciate you persevering with me

    I am attaching a full version of the sheet so far. I realise I could shorten some of the formula by naming lists but I would rather have a working version first and then tune it later

    You are right with your assumption, I would like the Dropdown box to dictate which sheet the prices are returned from as the ranges are priced across 5 groups.

    Regards (again)
    Andy
    Attached Files Attached Files
    Novice Excel Dabbler

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Thanks, this is beginning to make a little sense. However, what is the difference between the values on the Data Sheet for Group 2 and the values on worksheet Group 2?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Feb 2008
    Location
    Gainsborough
    Posts
    20
    I put the ranges in to groups to try and make it easier for myself to work through the lookup formula. On the data page are all the prices for all the ranges one after the other in no particular order.

    If it is more straightforward to use just the 'Data' sheet then I'm more than happy to work my formula again I just need a pointer as to how the dropdown will select the correct prices for the range that was selected.

    Would it be more simple to not use the dropdown as the key to the pricing and to use a different path?
    Novice Excel Dabbler

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, I think I have your solution.

    I shortened all worksheet names to make references simpler. And with your Data Sheet set up, there is no need for the Group sheets unless you need them.

    So, on the CustomerPriceList worksheet, in cell D6, put this formula in:

    =INDEX(Data!$A$2:$DR$475,MATCH($C6,Data!$A$2:$A475 ,FALSE),2)

    Then in E 6, put this formula in:

    =INDEX(Data!$A$2:$DR$475,MATCH($C6,Data!$A$2:$A475 ,FALSE),MATCH($E$3&" "&E$5,Data!$A$2:$DR$2,FALSE))

    and copy to the right.

    Notice that this uses the drop down of the City.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    Oct 2003
    Posts
    1,091
    Basically INDEX/MATCH allows you to do two way lookups (row and column). I concatenated the City name and the headers (with a space between) so that it could be used as the lookup for the proper column on Data.

    BTW, you have Data laid out in a very usable form and easily read form. Well done.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  11. #11
    Join Date
    Feb 2008
    Location
    Gainsborough
    Posts
    20

    Thumbs up

    Thank you for the compliment
    I read up the basics for making a start and then I got in too deep.

    I'm grateful for your help and if its appropriate heres a beer on me


    Sheet works brilliantly

    The Sales guy won't know what hit him tomorrow when I implement it!

    Many thanks
    Andy
    Novice Excel Dabbler

  12. #12
    Join Date
    Oct 2003
    Posts
    1,091
    Great, and glad to help. Let them drool over your work. It is well done.

    And again, 75% of using data starts with good data structure. Once I figured out your structure, the rest was relatively easy.

    Note also, that I did not include any error trapping in the formulas. But since you have done that with the VLOOKUP formulas, I assumed you could do it with this set of formulas as well.

    You have moved beyond the "Novice Excel Dabbler"!

  13. #13
    Join Date
    Feb 2008
    Location
    Gainsborough
    Posts
    20
    I'll 'Dabble' a little more and put the error trapping back.....Don't go away!!!!
    Novice Excel Dabbler

  14. #14
    Join Date
    Feb 2008
    Location
    Gainsborough
    Posts
    20
    I'm back again

    I've been tweaking a little and now got a sheet set up for the Sales guy to use but I would like to add a little extra to the tail of the sheet which will allow him to input the quantity of accessories instead of an individual line for each item

    As the formula is now far in advance of where I'm at I would like to now how to Insert a PRODUCT in the last 10 rows to calculate the correct value in the Columns after the Subtotal line

    Its probably a lot easier than my small brain thinks but I've tried several times and just end up with a circular that is annoying me!

    Regards
    Andy
    Attached Files Attached Files
    Novice Excel Dabbler

  15. #15
    Join Date
    Oct 2003
    Posts
    1,091
    Are you wanting the number of each unique item in column C?
    If so, then in cell F47, enter this formula and copy down.

    =COUNTIF($C$6:$C$45,$C47)

    This assumes you will select the appropriate item in cell C47
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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