Stock generate in drop down list

[rameshppc]

Hi Good evening,

I am always happy to get your magical solutions for challenging questions...

I am working in a excel which contains school bag, travel bag & lunch bag inward informations as below..[TABLE="width: 500"]
<tbody>[TR]
[TD]s no
[/TD]
[TD]date
[/TD]
[TD]bag description
[/TD]
[TD]rate
[/TD]
[TD]qty
[/TD]
[TD]total value
[/TD]
[TD]selling price
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]01/12/2018
[/TD]
[TD]school bag - A
[/TD]
[TD]380
[/TD]
[TD]1[/TD]
[TD]380
[/TD]
[TD]490
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]01/12/2018
[/TD]
[TD]school bag - B
[/TD]
[TD]350
[/TD]
[TD]2
[/TD]
[TD]700
[/TD]
[TD]460
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]02/12/2018
[/TD]
[TD]lunch bag - A
[/TD]
[TD]65
[/TD]
[TD]1
[/TD]
[TD]65
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]02/12/2018
[/TD]
[TD]travel bag - A
[/TD]
[TD]250
[/TD]
[TD]2
[/TD]
[TD]500
[/TD]
[TD]400
[/TD]
[/TR]
</tbody>[/TABLE]

I need to pay the manufacturer, once its sold. so i have below excel to follow this,
[TABLE="width: 500"]
<tbody>[TR]
[TD]s no
[/TD]
[TD]sold date
[/TD]
[TD]bag description
[/TD]
[TD]sold qty
[/TD]
[TD]sold price
[/TD]
[TD]total value
[/TD]
[TD]pay manufacturer amount
[/TD]
[TD]issued amount
[/TD]
[TD]issued date
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]07/12/2018
[/TD]
[TD]school bag - B
[/TD]
[TD]2
[/TD]
[TD]600
[/TD]
[TD]1200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Here i need the stock information in bag description column as a drop down list.
If i sold the school bag - B 2nos, then next row this bag description should not show in drop down list.
Also need your suggestion to find the stock quantity also...

I will thanks a lot, which helps me to save the time for working with this excel chart...

 

[JustynaMK]

Thanks! A few things need changing. Please follow these steps and let me know if any of these steps do not work for you:

1. Sheet3 - column B currently contains your drop-down list, which is not working. Please insert a new empty column B (your drop-down lists will shift to column C). In cell B2 enter this Array formula (enter with Ctrl+Shift+Enter):
   

   =IFERROR(INDEX($A$2:$A$20,SMALL(IF($A$2:$A$20<>"",ROW($A$2:$A$20)-1,""),ROW(B1))),"")

   Copy it down to cell B20.
   
   What you should see right now is exactly the same list as in column A, but with blanks removed.
2. Click on cell C2; Data > Data Validation > Source:
   

   =OFFSET($B$2,0,0,COUNTA($B$2:$B$20)-COUNTBLANK($B$2:$B$20),1)

   Now your drop-down list in cell C2 should contain all entries from column B.
3. Remove OFFSET formula from cell E5, we won't need it anymore.
4. If you wish to use the same drop-down list on other sheets (e.g. INWARD, cell K5), you need to use the following formula:
   

   =OFFSET(Sheet3!$B$2,0,0,COUNTA(Sheet3!$B$2:$B$20)-COUNTBLANK(Sheet3!$B$2:$B$20),1)

5. Now, if you want to improve your formula and start checking the Quantities sold, you need to go back to Sheet3, cell A2, and use:
   <inward!e3,inward!c3,""),"")
   

   =IF(LEN(INWARD!C3),IF(SUMIFS(INWARD!L:L,INWARD!K:K,INWARD!C3)<INWARD!E3,INWARD!C3,""),"")

   Copy & paste it down to cell A20.</inward!e3,inward!c3,""),"")

 

[rameshppc]

I am very much happy to see your step by step explanations...

And i will respect your effort to make my expectation as possible...

I followed as per your instructions and got trouble in point no. 4 & 5

When i apply the formula in sheet1 --> K5 cell, "You cannot use references to other worksheets or workbooks for data validation criteria"

When i apply the formula in sheet3 --> A2 cell, "Your formula is missing a parenthesis --) or (. check the formula and then add the parenthesis in the appropriate place"

 

[JustynaMK]

It might be you are working with an older version of Excel; some of them do not allow you to create validation references from another sheet.
Let's try creating a Named Range and then you can reference it in your Data Validation.

Go to: Formulas > Name Manager > New... >
Name: rng_BagType
RefersTo: =OFFSET(Sheet3!$B$2,0,0,COUNTA(Sheet3!$B$2:$B$20)-COUNTBLANK(Sheet3!$B$2:$B$20),1)

Now go back to INWARD Sheet, cell K3, Data > Data Validation > Source > delete the old formula and press F3 > choose rng_BagType > OK
Hope this solution works for you (cannot recreate it on my side as I'm on Excel 2013 / 365).

As for the last formula - sorry about that; the forum doesn't interpret formula signs properly and this is the second time that this formula was cut in the middle. Let's try it with spaces:
=IF(LEN(INWARD!C3),IF(SUMIFS(INWARD!L:L,INWARD!K:K,INWARD!C3) < INWARD!E3,INWARD!C3,""),"")

 

[rameshppc]

Many thanks to you, for making my excel work as simple...
I will learn from your formulas and worked out myself in future..

I really thank again for put your effort here..

 

[rameshppc]

Hi justy..

Sorry to not check this myself..

The drop down list not working in INWARD!c3:c34, after pasted the formula =OFFSET($C$3,1,0,MAX($T:$T),1) in the data validation.

Other all working perfectly..

 

[JustynaMK]

Glad it worked fine for you!
As for the drop-down list - yes, I also see it in your original file. What is the purpose of this drop-down list? Should it present items from the SOURCE sheet?

 

[rameshppc]

Because the INWARD!D:D having auto filling rate from the source file.

 

[JustynaMK]

Sorry, not sure if I understood it correctly. In INWARD sheet, column C, there is a drop-down list which currently doesn't work. Column D returns VLOOKUP function results (column B from the SOURCE sheet).

If the source of your drop-down list (column C, INWARD sheet) is supposed to be Column A from the SOURCE sheet, you can do the following:
1. Go to SOURCE sheet, Formulas > Name Manager > New (rng_Source) > Refers To:
=OFFSET(SOURCE!$A$1,0,0,COUNTA(SOURCE!$A:$A),1)

2. Then go back to INWARD sheet, cell C3, Data > Data Validation > remove formula from the "Source" field, press F3 and select "rng_Source".
This should give you a drop-down list of all items listed in the Source sheet, column A. Copy it down to the remaining cells.

 

[rameshppc]

Hi justy,

I have updated your formulas in my file

still the stock generation not works properly in "INWARD!k:k" pls open the below link and look k4 to k9 the drop down list not omitting the bag descriptions, which are not available in stock.

https://www.dropbox.com/s/7wut5qkk4hm041x/BAG BUSINESS (1).xlsx?dl=0

 

[JustynaMK]

Hey rameshppc,

Hope everything is well. I just reviewed the file and it seems like we have a little misunderstanding. The solution that I have provided in my latest post was for INWARD sheet, column C (the answer to your post: "The drop down list not working in INWARD!c3:c34, after pasted the formula =OFFSET($C$3,1,0,MAX($T:$T),1)"). So Data Validation that you are currently using in column K should be actually used in column C.

As for column K, you need to follow instructions that I posted on December 14th - you confirmed that this solution was working correctly, so if you have any issues with these steps then please let me know.

Take care,
Justyna