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...

 

[rameshppc]

I understand that, its really challenging one...

I think some one analyzing and get back me soon...

Thanks..

 

[JustynaMK]

Hi rameshpcc,

I am basing my solution on three assumptions:

1. Your first table is in Sheet1, cells A1:G5
2. Your second table is in Sheet2, cells A1:I2 (both assumptions ensure that your "bag description" item is always present in column C)
3. Your maximum data range is 20 rows

You can now create Sheet3 and calculate the following:

Cell A1 label -> "Sheet1 - Sheet2"
Cells A2 to A20 formula ->
=IF(LEN(Sheet1!C2),IF(ISNA(VLOOKUP(Sheet1!C2,Sheet2!C:C,1,0)),Sheet1!C2,""),"")
You will notice that column A presents all "bag description" items extracted from Sheet1 excluding those already selected on Sheet2.

Cell B1 label -> "Drop-down list"
Cells B2 to B20 formula (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))),"")
Column B presents exactly the same information as column A, with blank rows removed.

Now the only thing remaining is to create your dynamic drop-down list.
Data > Data Validation > List > Source (formula):
=OFFSET($B$2,0,0,COUNTA(B2:B20)-COUNTBLANK(B2:B20),1)

Hope it works for you. Let me know if anything is unclear.

 

[rameshppc]

Hi Justy,

You are great in finding solutions...

i am getting an error while doing drop-down list in sheet 3 -> b2 to b20 as "The list source must be a delimited list or a reference to single row or column"

Can you pls help me on this...

And, i want to know how this formula will work on below case,

If the available stock of bag description "School bag - B" is "2" (qty) and if i enter the bag description "School bag - B" is "1" (qty) in sheet 2,

Then, may i know the balance qty "1" will show in drop down?

 

[JustynaMK]

Hey,

I think it might be caused by the lack of absolute references in our "dynamic drop-down list" formula. Try fixing it as follows (Data Validation > List > Source):
=OFFSET($B$2,0,0,COUNTA($B$2:$B$20)-COUNTBLANK($B$2:$B$20),1)
Once fixed, stay on "Data Validation" window and click on Source formula window. If the formula is working, it should highlight the correct range in column B.

As for your second question, that's not how it is currently designed - once you select a given item, it will disappear from the drop-down list. If you want to include the Quantity check, then you need to sum the Quantity sold (Sheet 2) and compare it with a number of available items (Quantity in Sheet1). Simply replace formula on Sheet3, right under "Sheet1 - Sheet2" header, with the following syntax:
=IF(LEN(Sheet1!C2),IF(SUMIFS(Sheet2!D:D,Sheet2!C:C,Sheet1!C2)<Sheet1!E2,Sheet1!C2,""),"")

 

[rameshppc]

hi justy,

I got an error as "The source currently evaluates to an error. Do you want to continue?"

If i click the ok button, the dropdown arrow mark in b2:b20 not working..

 

[JustynaMK]

It's hard to tell what is causing an error. Would it be possible for you to upload the file and share the link here? I'll then check what exactly is happening.

 

[rameshppc]

Hi justy,

pls see the below link and advise me to proceed for further..


https://www.dropbox.com/s/tj69d6wog6pv6vx/BAG BUSINESS.xlsx?dl=0

 

[JustynaMK]

Cool, thanks! Don't have access to dropbox on this machine - I'll check it later today and let you know the results.

 

[rameshppc]

Thanks and waiting for your amazing results...