• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for creating Dynamic Dependant dropdown with unique values

Aswinraj

Member
Hello Sir/Madam,
Please Please help me on this below issue in Macro.

Having issues in 1. creating Dynamic Dependant dropdown with unique values - In Billing Sheet Model Name and Size and color should be in dropdown (Data should be fetched from Stock Sheet).
Based on above criteria it should display the Available quantity.

2. If Sale Qty is 30 or greater than 30 then Sale Type should be "W" else "R",
If sale type is "W" then it should fetch the data from Whole sale
If sale type is "R" then it should fetch the data from Retail

3. Date should be Current date and Total (J Column)should be Sale Qty*Price(In I Column).

I am struggling for the past one Month. Please help whatever you can from these steps. Please Help Me.
 

Attachments

  • Stock_Bill(1).xlsm
    46 KB · Views: 6
Hello Throttleworks, Thanks for your warm welcome.

I had modified my first thread and added the Sample file. If you could please add the coding to the existing file.
 
Hi throttle, any update on this query.. please help me.. the link which you had provided is not sufficient.. I had attached the excel file in my first thread.. kindly help.., waiting for the solution..
 
Hi Vletm,

Yes i think this is enough, but the date in Billing should be the Current date - Should not fetch from Stock (Purchase date).

Can you please look into other issues which i am facing. Please
 
@Aswinraj
Modifications:
Date is Current Date.
'Avibl Qty' still missing.
[Form Reset]-button clears Form.
With [Search Customer], You can search which any of three information.
There are still some things to do ... like cell [F29]
Next time more, after Your testings
 

Attachments

  • Stock_Bill(1).xlsm
    65.4 KB · Views: 3
Hi Vletm,

Thanks a lot for your update. I had tested and i believe the dropdown is not working dynamically.

I could identify that i Stock sheet - you had taken the data to dropdown from G,H,I,J columns and M,N,O,P columns but when i add the data to Model Name (G) and Size (H) the data is not available in dropdown box and when i add in Column M, then its available in dropdown box which is two way work. Can you please modify it.

Ie.., When i add the data in G,H,I Column - it should fetch those data in dropdown box.

2. In Billing Sheet: Sale Type formula should be IF(F7>=30,"W","R") - price should be fetched based on it. If it is W (Should fetch the data from Wholesale Sheet). If it is R (Should fetch the data from Retail sheet).

3. Form Reset is not working for me When i click that button it shows Error "400".

Search, Date, total price is working fine :)

Awaiting for your reply as well as update.
 
@Aswinraj ... It works ...
You just have to do one click after modify Stock. I added that button.
Yes, there were >30, now there is >=30.
There were one shortcut, not good, how to fetch prices.
There is visual indication of missing prices.
Form Reset and Merge cells, now Reset Form works.
I added Pick Customer function. The most easy to use.
Avalbl Qty is now same as Sale Qty. Total has a formula, if Sale Qty changes.
Still cell [F29] not good! no meaning?
How do You 'send' this ... [P22,P23] ?
... after opening that file, do You want default Reset Form?
 

Attachments

  • Stock_Bill(1).xlsm
    74.7 KB · Views: 3
Hello Vletm, Sorry for the delay response since I was not feeling well. :)
1. Everything is working fine other than Sale type - Example: IF(F7>=30,"W","R") If Sale Qty >=30 then Sale Type should change to W. If it is W then the price should fetch from Whole sale Sheet based on product. If it is R then the price should fetch from Retail sheet based on product.
2. I think the Pick Customer button is not necessary.
3. No need default reset form since there is a reset form option already provided.
4. F29 in the old excel which i had sent, its not F29 but G29 - From G7 to G28 if R is greater than W then G29 should reflect R. If W is Greater than R then G29 should reflect W. (Hope you got my point) :)
5. Purchase sheet: When new products are added Manually to Purchase sheet - then Model, Size, Color and Quantity should added to the stock. If any changes done in the existing Purchase data then it should reflect in the stock.
Example: Purchase sheet contains the data: Shirt A, 40 , Black ,10..,
If I change the count from 10 to 6 then the same quantity should reflect in the Stock as well.
If new data added in the Purchase sheet, then it should reflect in the Stock as well.
6. Like wise for Sales Sheet. when data is added in sales manually based on Model name,size,color and quantity it should remove from Stock.
Example: If Condition 1,2,3 matches then remove the count given in the "Qty" from stock.
 
@Aswinraj
I did minor modifications to layout. ( Avl | W | R )
1. I hope that now W/R works ... also then change 'Sale Qty'.
2. No need to worry about miswriting ...
3. Okay, You'll do it manually. [Row] clears row :)
4. Test this point.
5. & 6. You're missing one information! Number of Documentation?
I can 'add' [Billing]'s data to [Sales]'s data and
if if [Billing] is 'Purchase', it could add it's data to [Purchase]'s data same way.
[Stock] should be [Purchases] - [Sales]
If You'll use this without the whole information, there should be 'previous'-versions of those sheets. Then it's possible to know what to change...
Gotta think ...
 

Attachments

  • Stock_Bill(1).xlsm
    78.9 KB · Views: 2
@Aswinraj ... next version for testing.
1) home page
2) Sells/Purchases, both with Billing-form
3) Apply Form -> Stocks
4) Idea: New 'Model' to Stock ...
I'll open these modification later...
 

Attachments

  • Stock_Bill(1).xlsm
    104.1 KB · Views: 10
@Aswinraj
You wrote 'working fine' and .. awaiting the changes...
Many of those work there already ...
Did You read my replies #11 &#12?
You can do both, 'Sell and Purchase', with 'Billing'-sheet.
Both, 'Sell and Purchase', will change values of 'Stock' too.
If You'll make those manually (by written on those sheets...)
how to know which information should move to 'Stock' too?
With 'Billing', You can add many rows in same time.
If You get a new model, You add should add it to 'Stock' in correct places manually and it's prices to 'Wholesale' or 'Retail'-sheets manually.
...and everywhere else, there should be 'a document number' with every changes of 'stock'.
 
@vletm,

I had tested the new buttons, but still when the dropdown is selected in Billing, E,F,G,H,I is not updating as i mentioned in above queries instead it was updating in K,L,M,N.
 
@Aswinraj Do You mean on 'Billing'-sheet?
1) select 'Model Name' => Shirt A
2) select 'Size' => 40
3) select 'Color' => Black =>=>=> | K100 | L180 | M150 | N3
4) write 'Sale Qty' 4 =>=>=> | FW | G30/11/2015 | H180 | I720
K,L,M,N can come after 'Color' selection, not before
F,G,H,I can come after 'Sale Qty' value, not before
Ideas?
 
In Billing sheet: When i select Model Name, Size, Color and entered Sale Qty - it should display Sale Type, Date, Price, Total in respective columns (F,G,H,I)
but instead it was displaying in K,L,M,N Columns as Avl,W,R, Srow - which should not be.
 
@Aswinraj
There are two modes, selected with MODE-button
Purchases - case after enter Sale Qty to row 4
Screen Shot 2015-11-30 at 16.05.44.png
and Sales - case after enter Sale Qty to row 3
Screen Shot 2015-11-30 at 16.06.34.png
... and You scroll sheet lower
You'll press [Apply Form], even Stock-values will change.
 
Hello Vletm,
Apologies for the delay.., I could not able to make it.,
I had tried several times based on your screen shot provided above. But it doesnt work.., Please help me on this to fix it.upload_2016-1-5_17-9-25.png
 

Attachments

  • Stock_Bill(1).xlsm
    104.1 KB · Views: 1
@Aswinraj
1) Did You really read #18 reply?
2) The photo tells, that You're purchasing 'Shirt A, Size 40, black'.
When did You get that message?
after ... selecting 'Model Name' or 'Size' or 'Color' or when?
3) Why You didn't save that case in that file too?
 
@vletm, Yes i read it..,
My Question is when i enter the Sale Qty 30 or Greater than 30 then the Sale Type Should Display as "W" if it is below 30 then it should display as "R" and as well as Date, Price and Total Price is not displaying.
As you asked, i saved it and attached the same file, as well as screen shot below.
When i Enter Sale Qty it should display Sale Type, Date, Price, Total but it doesnt

View attachment 25632

upload_2016-1-5_17-50-30.png
 

Attachments

  • Stock_Bill(1).xlsm
    90.3 KB · Views: 3
Last edited:
@Aswinraj
1) You skip my question! So You didn't read #18 reply!
My answer is again.
You are purchacing (= buying) in that photo,
there is text Purchasing Billing,
then You don't get those values; ReRead #16 Reply!
I really think that You cannot name prices then You're buying,
the seller will tell prices!

If You are ' Sales Billing ' = selling (mode) then You will get those values,
as I have written many many times!
You can change that MODE with [Mode]-button.
ReRead #14 Reply!
 
@vletm, ofcourse you said it before but its not working. Even when i choose Sales Billing still the issue exist as i said before. Even you can check the same in my attachment in the previous reply.
upload_2016-1-5_19-55-41.png
 
ok let make it simple., i will make some changes and come back to you in next post.., hope it will be easy for you to help me or fix it, Apologies.,
 
Back
Top