+ Reply to Thread
Results 1 to 11 of 11

Showing dependent on another column list and checking stock

  1. #1
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Showing dependent on another column list and checking stock

    Hello.

    I have 2 Sheets. In one I have set 3 product categories, each one has any Specifically products.
    At the same sheet, there are all products with available quantities.
    On the other Sheet, that is for orders, in column C, the user selects from a list of product category. In the next column(D), I like to display a list of products only in this category and only if quantities are available for this product.
    So this means that any quantity of any product, bound to order, should be removed from stock this product
    I attach you, a test file, to better understand my point.

    Thank you in advance
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  2. #2
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Showing dependent on another column list and checking stock

    Hi Fotis,

    Please check the attached sheet. Do let me know if it helps or if any changes are required.
    Attached Files Attached Files
    One must rise above the Clouds to see the Blue Sky rather than constantly trying to push them aside

    If you want to say Thank you to a member, click the little star * below.

    If your Problem is Solved, please mark the Thread as Solved

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Showing dependent on another column list and checking stock

    Dear Inayat.

    Thank you very much for your reply. I appreciate it.
    I noticed in my attachments, explaining I had written the numbers from cells obviously wrong and probably confused you. Apology for that.

    I think the new attachment, I am clear about what I want to do.
    Attached Files Attached Files

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Showing dependent on another column list and checking stock

    Hi!

    Any proposal?...........

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Showing dependent on another column list and checking stock

    Hello again.

    May i assume that what ι ask, can not be done?

    Thank you in advance.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Showing dependent on another column list and checking stock

    This requires macros, unless you create a separate validation list for every row of data.

    On Stock, I created three additional lists, one for each category, to include only items that have stock >0. Each list is given a range name, which is the name of the category with the spaces removed (spaces are not allowed in range names). This step is a manual one-time setup. (Named ranges are required because a validation list cannot refer to a range in another worksheet unless it is a named range.)

    The code detects whether a value in column C changes. If so, it updates the validation list for the item in the row of column D to match the correct list for that category.

    One inconvenient feature is that the dropdown lists will have gaps for the items that are out of stock. I think it is possible to compress the lists with more complex formulas but I haven't focused on that.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Showing dependent on another column list and checking stock

    Hello to all.

    Dear 6StringJazzer, Thank you very much for your effort. I really appreciate
    As we understand it is quite complex and difficult issue to resolve.
    In your example, only the first line works properly.
    I found the forum a very good job of Marcol, but my knowledge is obviously to adapt to me.
    Probably should start disappointed ....

    [http://www.excelforum.com/excel-gene...sed-cells.html

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Showing dependent on another column list and checking stock

    Quote Originally Posted by Fotis1991 View Post
    In your example, only the first line works properly.
    I tested it and every line works. What is happening that doesn't work?

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Showing dependent on another column list and checking stock

    Hi again.

    When I say it only works the first line, I mean that I choose a category B in cell C2 and in cell D2, It proposes products from 6 to 10 as well to do.
    On C3,when i choose a category, then in cell D3, the list suggests the same as the previous cell (Products from 6 to 10). Instead to suggests the products from 1 to 4 (because the product 5, not available in stock).
    The same applies to the next line.

    From line 5 to 9 it's OK, but please try to add a new date in B7 for example and look what happens. The same is if you try to write, let's say 50 in F5. Also in column E, there is problem with stock quantities
    Looks to me some problem to VBA......

    Thank you for your time.
    Attached Files Attached Files

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Showing dependent on another column list and checking stock

    When I do what you describe I get the correct results.

    The VBA code will only update the lists in column D if the cell in column C is changed. That is, if C3 was "Category B" before I wrote the macro, then D3 is not automatically changed to have the correct list until you select something else in C3. If you think that the value in D3 is incorrect, then select a different value in C3 then re-select the original value. Then check D3 again.

    I had no problem entering dates in column B.

    When I entered 50 in column F, I got an error because there is data validation there, which you provided before I made any changes. The data validation formula for all cells in column F is

    =SUM($F$2:$F$13)<$E$2

    I cannot make any sense of this. This means for a quantity entered in any row of column F, it is required that the sum of all order quantities for all products must be less than the available stock of the item on the first line. I think you want

    =F2<=E2

    This means that the order quantity for that item must be less than or equal to the available stock for the same item. I updated this for you.

    Your VLOOKUP formula is incorrect. It uses relative references instead of absolute references for the lookup table. In E2 use this formula and copy down:

    =VLOOKUP(D2,Stock!$A$3:$B$17,2,0)

    I updated this for you.
    Attached Files Attached Files

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Showing dependent on another column list and checking stock

    Hello from Greece.

    Dear 6StringJazzer

    Thank you very much for your help. It was valuable and showed me step by step method to find my subject.
    Because my relationship with the VBA is not good, the repealed and replaced in column D, the function INDIRECT, which gives me the same result.

    Once again thank you very much for your valuable help.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1