Hi good afternoon,
I am managing my orders in this attached chart easily, but the color filling and update the information in this chart takes too much time.
I am filling the Sheet "Input data" regularly based on my incoming order details. but the sheet "Auto generated" I need your help to auto generate based on the information in sheet "Input data"
The criteria of sequence of the "style no" based on earliest date in "Ex-factory date"
I could do this with a VBA solution. Are you happy with that?
Do you know how to incorporate a VBA macro into your workbook?
If so I have a couple of questions (I don't want to add complications that aren't required)
1. Are you limited to 13 styles and will they always be in the same order on those two sheets?
2. If there are more style numbers created, will you add them manually to BOTH sheets with all the formatting etc.?
Last edited by Croweater; 05-23-2023 at 11:17 PM.
Hi croweater,
VBA is ok. 13 styles is enough. If more styles required, as you said i will take care of it.
Very confusing as to what you really want, especially as it looks like there is an error in your representative sheet!
Look at the ex factory date for style 7358V. Input Data and auto generated data do not match?
Anyway, guessing as to what dates you input on your data sheet and how you want your auto generated sheet to look, try this macro...
Please Login or Register to view this content.
Hi croweater,
Really sorry for that date mismatch for 7358V. Its my mistake and for avoid this mistake only i need auto generation.
I am using excel 2013 and i have used your code in macro. Also i have changed my excel in to macro enabled worksheet. but while the macro runs, its take some time (cursor blinks) and the end of process, "microsoft excel stop working" error came.
Can you pls help on this? i am new to macro's
Well I ran it on your representative data using my clunky old but trusty machine running Excel 2003 and it was over and done with in an instant.
You said 13 styles was 'enough', so how many Purchase Orders do you have for each of those 13 styles (how many columns on your input data sheet)?
When you say it takes 'some time' that is very subjective. How long does it take 2-3 seconds, 20 seconds, 5 minutes?
There are some things we can do to speed it up. Can you answer those questions so I can get something more realistic to what you have and that I can benchmark some timings for?
It could also be that your workbook has something in to cause the code to go into an infinite loop.
Can you put up your sheet which took 'some time'?
Last edited by Croweater; 05-25-2023 at 06:06 AM.
Hi Croweater,
maximum 13styles and 10 po's is enough.
The time taken for macro's run is 10 sec
After see your 1st line of msg. i have replace the macro codes again and run it again. Now there is nothing happen in the "auto generated" sheet.
I request you to kindly send me macro updated excel file instead of codes.
See attached
Hi croweater,
No doubt, you are genius....
small changes need in this file to work professionally.
Every colored part in "Auto generated" sheet starts@ ex-factory date and ends@ PO date. But, If i change the date in "Input data" sheet, the colored part moves only based on ex-factory date and not considering the Po date.
Also the color should be ends on the PO date. But now its ends on next day of PO date, and the date i filled while i have submitted the excel file to you.
Also the logic of different between po date and ex-factory date is not constant. If i change the difference in the "input data" sheet. It should be reflect in "auto generated" sheet.
Thanks for your all efforts..
The way the input data sheet seems to work is that you put the PO date in and the Ex Factory date is ALWAYS 14 days prior to this. There is a formula that calculates the ex factory date.
Is that not the way you want it?
Same as the colour. Your representative data showed the colour extending to the order quantity. Is that not right?
Not sure what the last bit means.
Can you put up a sheet highlighting an example of each of the issues you raise and show how you want the output to look?
The way the input data sheet seems to work is that you put the PO date in and the Ex Factory date is ALWAYS 14 days prior to this. There is a formula that calculates the ex factory date.
Ramesh h&m : Yes. the difference b/w po date and ex-factory date is always same for all styles. but it's not constant value. it may differ based on shipment mode. so kindly consider both dates.
Is that not the way you want it?
Ramesh h&m : i have attached "expected output" excel file in this msg. kindly review the comment added in individual cells to understand clearly.
Same as the colour. Your representative data showed the colour extending to the order quantity. Is that not right?
Ramesh h&m : yes. the color part starts@ ex-factory date and ends@ PO date. If i change the difference in the "input data" sheet. It should be reflect in "auto generated" sheet.
Not sure what the last bit means.
Ramesh h&m : I cant understand, where i was mentioned.
Can you put up a sheet highlighting an example of each of the issues you raise and show how you want the output to look?
Ramesh h&m : pls see the difference b/w "auto generated" sheet and "expected output" sheet. it will helps to understand the actual requirement.
modified
Please Login or Register to view this content.
Hi croweater,
I have updated new macro codes in attached tracking chart. but its still looks different.(ex.style numbers not get auto updated)
I don't know what you mean by "ex.style numbers not get auto updated".
The only differences I can see between the autogenerated output (using the macro) and your Expected output sheets are;
1. Your style numbers in your expected output are in a different order across your sheet than they are in the TRACKING CHART sheet where the macro was run.
Remember my very first question I asked? i.e. Are you limited to 13 styles and WILL THEY ALWAYS BE IN THE SAME ORDER ON THOSE TWO SHEETS (i.e INPUT DATA and AUTO GENERATED)?
2. Your EX FACTORY date on your Expected output sheet is one day LATER than what it is on your INPUT DATA Sheet. Do you really want the EX FACTORY date on the AUTO GENERATED sheet to be one day LATER than what it is on the INPUT DATA?
You have already got your 'expected output' wrong once. Are you sure you haven't got it wrong again?
OK....It has just dawned on me what I THINK you mean. Do you want the data in the AUTO GENERATED sheet sorted after you have run the macro so that the earliest EX FACTORY date is in the left most column?
If so there are a couple of things you need;
1. Put this formula into cell D1 and drag across as far as you need
=IFERROR(MATCH(FALSE,ISBLANK(D3:D999),0),999)
You can hide this if you like by giving a custom format of 3 semi colons ;;;
2. Use this sort macro
see attached.Please Login or Register to view this content.
Please note that my original code assumes the styles are in the same order as in the INPUT DATA sheet, so once you have sorted it, that macro won't work with the sorted data.
That is why I asked you that question about the styles being in the same order on the two sheets.
If this is a problem (I'm not sure how you use this sheet, it may not be), then I will need to change the auto generate macro, which will be trickier because you are using merged cells for the styles in the INPUT DATA sheet. (Tsk Tsk)
Last edited by Croweater; 05-31-2023 at 10:57 PM.
OK...I've made some changes so you can go back and forth between autogenerating and sorting as many times as you like.
Last edited by Croweater; 06-01-2023 at 05:43 AM.
Hi croweater,
I have changed some data in "input sheet" and deleted old datas in "auto generated" sheet and then i run the macro, i am getting popup window as "Type mismatch"
All your Style Nos in the AUTO GENERATED Sheet are blank!
Hi croweater,
Yes. Because i have tried to insert new data in input sheet and deleted old style nos in auto generated sheet and then run the macro.
So put the new Style Nos back in the auto generated sheet (like you said you would do in post #3), run the two macros and get back to me.
Re: Filling color in-between two dates in chart
I tell you what, because I am such a nice guy and you said I was a genius, the attached version actually puts the new style numbers in the autogenerated sheet, if you have left them blank.
The next time I come to India, you can buy me a Kingfisher.
Hi croweater,
sorry.. I think this chart make you so tired. Now its absolutely what i expected.
Then i am sure, a kingfisher is ready for you in India..
Glad you are happy with it. Thanks for the feedback/rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks