Macro for creating Dynamic Dependant dropdown with unique values

[Aswinraj]

@vletm:
1. its ok for 'Sale Billing' but the same should apply for 'Purchase Billing' too.., Can you please make it..???? Its a customer need.
2. Sale Type should be W when it is on or above 30 and R when it is below 30.
3. When Sale Billing is happened, then it should be reduced from Stock count from Stock Sheet.
4. When Purchase Billing is happened, then it should be added to Stock count from Stock Sheet.

Can you help me on this.,

 

[vletm]

@Aswinraj
1) 'Purchase Billing' .. really that You can name prices?
So, far 'Purchaces'-sheet don't have need to save prices, check please.
How do You think to make
if You buy 25 qty (=W) and 55 (=R) same 'Shirt A 40 Black'.
You have to mark two different prices, or how to do?
You can see stocks prices with 'Purchasing' too.
Do You really sell thing with same prices?
Here, if purchase price is 20 then selling price is 30 or more.
if buy a lot (your sample >30), then price would be 15 and selling price would be 22 or more.
2) that 30 step and if You write NEW price could save in proper place.
Do You mean something like this:
using 'Purchasing' ... select 'model' -> 'size' -> 'color' -> write 'qty' -> it could mark 'W/R' -> you should write 'price'
after all rows done press [Apply Form] ->
prices would update (WorR),
purchase-sheet would update and
stock would update?
3&4) Have You tested to do selling or buying? A long time ago, it worked!

 

[Aswinraj]

@vletm,
Sale and purchase items update in STOCK Sheet - works fine.

Now i added the Price and Total column to Purchase Sheet.
before proceeding to Purchase Billing, lets finalize Sales Billing.

There is an Minor new change needed for Sales Billing.
1. Based on Total Sale Quantity - Sale Type should be displayed (if Sale quantity >=30,"W","R") )and when we press VALIDATE Button. Based on Sale Type if it is W then it should fetch the price for all the items choosen from Whole Sale price. If it is R then it should fetch the price for all the items choosen from Retail Price.

Can you please do this change.,. I had attached the file..,

 

[vletm]

@Aswinraj
1) Price & Total are Okay for next version.
2) Hmm ... there are NO MINOR new changes ... only possible changes
'VALIDATE'-case:
if there are more than ONE row,
30 Shirt A - size40 - W150/R180
1 Shirt B - size40 - W165/R230
1 Shirt C - size40 - W275/R350
=> tot 32 => offer 'W' =>
if press [VALIDATE] then all three Prices are 150 & 165 & 275 ?
YES or NO?

 

[Aswinraj]

YES.., Let me explain clearly..
Example: User has entered 3 Rows of Sales data.,
1. Shirt A - Size 40 - Color Black - Sale Qty = 10
2. Shirt B - Size 42 - Color Green - Sale Qty= 17
3. Shirt C - Size 41 - Color Black - Sale Qty= 3
then the Total Quantity E29 will be 30 - So Sale Type should be displayed as W. When User clicks Validate then for all 3 items it should fetch the price from Whole sale Since Sale Type is W. If the sale type is R then it should fetch the price for all 3 items entered in Retail.

(Previous Version we use to check for Whole sale or Retail for each individual Sale Qty but now instead of checking for each line items we need to whether Total Quantity is >=30 if it is greater than then Sale Type [above validate button] W will be displayed and based sale type when validate is pressed it should fetch the prices for all the data from either whole sale or retail)

 

[vletm]

@Aswinraj
1) 'Purchase Billing' makes data to 'Purchases' Price & Total
2) 'VALIDATE' =>
[ALL Wholesale]-button is visible only then 'Total Quantity >= 30'.
After press the button, You will have ONE change to cancel it.
There were some challenges, but please test this version.

 

[Aswinraj]

2. Validate Button: this is not my exact requirement. Can you please read my reply 28. I will explain it again. I had attached the excel with the modification.

In P Column, i had given SALE TYPE - Based on the value in E29 Column (Total Quantity) the SALE TYPE should display whether it is W or R.

VALIDATE BUTTON given below SALE TYPE - Once the VALIDATE BUTTON has been clicked then IT SHOULD DISPLAY ALL ITEMS PRICES BASED ON SALE TYPE.

1) Shirt A - size40 - W150/R180
2) Shirt B - size40 - W165/R230
3) Shirt C - size40 - W275/R350
=> Total Quantity - 32 => SALE TYPE - W =>
if press [VALIDATE] then all three Prices are 150 & 165 & 275

 

[vletm]

@Aswinraj
If less than E29 < 30 do You need to see that the new button?
Now, the new button is visible ONLY if E29 >= 30!
Isn't it same meaning with Your idea?
You can see which prices will change (check colors of prices).
Did You tried to 'sell' total over 30?
Or if E29 < 30 ( "R" ) and do You want to press the new button?
It means that all items would sell with "R" prices...
if so, then I'll understand and allow that button be visible all then time.

 

[Aswinraj]

@vletm, Yes the Validate button should be visible in default.

If Column E29 Total Quantity = 30 or 30 + then SALE TYPE (Q2) should be W and when we press VALIDATE then it should fetch all prices from whole sale and should calculate the total.

If E29 Total Quantity = 29 or below 29 then SALE TYPE (Q2) should be R and when we press VALIDATE then it should fetch all prices from retail and should calculate the total.

Note: E29, Column I and Q2 displays the formulas which should not be visible.

 

[vletm]

@Aswinraj
The next version ...
I took formulas away ... so not visible values.
... if 'E29' > 0 then 'ALL'-btn
If 'E29' < 30 then 'ALL Retail'-btn else 'ALL Wholesale'-btn

 

[Aswinraj]

@vletm, i tested and its fine All retail and whole sale button. Some Minor changes needed i believe. Let me proceed this file to my team and lets c.

Thanks for all your efforts and patience Thanks a lot as of now.