problems with if, and, isnumber, find arguments in a formula

[shina67]

Hi All,

I have a problem with a formula I have within my worksheet.
The formula is =IF(AND($F3=L$1,ISNUMBER(FIND($F3,$A$1))),$A3,"") which was suggested on another forum https://www.mrexcel.com/forum/excel-...k-formula.html
The formula is always returning a Zero but if the criteria is met I need it to return the value from $A3.
If the criteria is not met I need it to return a zero.
I have attached a link to the worksheet. The formula is in cells K3 through to cells Z3.
https://www.dropbox.com/s/u5apfhrm59...xcel.xlsm?dl=0

[azumi]

Not sure but put this on K3 and copied down cross:

=IF(AND($F3=SUBSTITUTE(K$1," ",""),$D3&$E3&$F3=$A$1),$A3,"")

[jason.b75]

edit:-

Moderation request removed. Thank you for adding the link to your cross post.

[shina67]

Jason,

My apologises. I did think that with it being a different formula and therefore a different problem that it would not be a cross post.
The cross post you refer to is at https://www.mrexcel.com/forum/excel-...k-formula.html

[jason.b75]

The formula I see in your first post here is the one that was suggested to you in the other forum, so I don't see how it is different.
You should edit your original post to include the link(s) to crossposts, when you add them as a reply later in the thread, they could be missed.

Also, for future referfence, we prefer that you attach sample files directly to the forum rather than on file sharing sites (Click the 'Go Advanced' button, then scroll down and look for the text link, 'Manage Attachments'. The paperclip icon does not work).

[shina67]

Jason,
Again apologises for this. My misunderstanding of a cross post obviously.
I have included the link to mr.excel post in my original post as suggested by yourself.

The formula I see in your first post here is the one that was suggested to you in the other forum, so I don't see how it is different.
You should edit your original post to include the link(s) to crossposts, when you add them as a reply later in the thread, they could be missed.

Also, for future referfence, we prefer that you attach sample files directly to the forum rather than on file sharing sites (Click the 'Go Advanced' button, then scroll down and look for the text link, 'Manage Attachments'. The paperclip icon does not work).

[jason.b75]

Have you tried azumi's suggestion from post #3?

The only problem I can see in your file is that you are trying to compare 'XP GLIDE' to 'XPGLIDE'. Because one has a space and the other doesn't, the 2 do not match which is why you get no result.

Azumi's formula looks like it should fix that.

If you want 0 instead of blank, simply change the "" at the end of the formula to 0.

[shina67]

Hi Jason,

Using the formula below seems to work fine,
=IF(AND($F3=SUBSTITUTE(N$1," ",""),$D3&$E3&$F3=$A$1),$A3,"")
The problem I now have is that when $A$1 is blank it does not remain at 6.

[jason.b75]

That would be because when A3 is blank, the criteria of finding F3 in A1 is no longer met.

Formula results update to reflect every change that you make to the data in the sheet.

[shina67]

Jason,

Is there a work around to this problem?

[jason.b75]

Workarounds would be circlular references (not recommended in your case), or vba (commonly referred to as a macro) instead of formulas.

As a solution, I would suggest changing the design of the sheet so that you don't need to delete anything.

[shina67]

Thanks Jason.
Not what I really wanted to hear
The design of the sheet is based on several other sheets that the information is gathered from.

[jason.b75]

Is it a template file? i.e. will you always be starting with a blank, then saving that as a new file with the data entered, in the sense that any numbers genereated by the formulas should not need to be changed once entered?

[shina67]

Hi Jason,

If I understand correctly what you are asking.
Columns G,H,I,J & A3 downwards are entered data from another sheet.
Everything else is formulas.
The formulas are all dependant on the input in $A$1 & $A$2.

[jason.b75]

Almost what I meant.

At any point, will you delete the data that you enter manually in order to re-use the sheet, or will you start a new sheet for the next project?

[shina67]

The sheet would remain the same with additional data entered underneath in columns G,H,I,J & A

[shina67]

The sheet would remain the same.
Additional data would be entered underneath in blank cells in Columns G,H,I,J & A.

[jason.b75]

Based on what you've said, this should work. Please test on a copy of your file, not the original!

Add this code to the worksheet module of the 'Schedule' sheet (right click the tab name, then view code). You already have some Worksheet_Change code in there, simply add this above, or below the existing code.

Please Login or Register  to view this content.

This will check the formulas in columns K:Z evertime they are calculated. Once the formula returns a number, that number will be entered directly into the cell, replacing the formula.
Does that help?

[shina67]

Hi Jason,

I have followed what you have said and now I am getting the following error which I have not had before.
This seems strange as it is not directly involved with your coding.
Capture.JPG

[jason.b75]

That didn't happen when I tried it in the test file.

What error message did it show?

[shina67]

The following is a snip of the screen.
Capture.JPG

[jason.b75]

See if this fixes it.

Please Login or Register  to view this content.

[shina67]

Thanks Jason for all your help so far.
I now have a new problem.

I’m hoping you can help with a drag down of formula’s.
Row 3 works fine for what I need it to do. However when I drag the formula’s down and then insert the source data into row 4 column G through to column Z there becomes a problem and downwards.
If in A1 I input 25412AYERSXPGLIDE.. and then a quantity into A2, row 3 updates as it should.
Then if I input 25944ELLISXPVIEW. into A1 and a quantity into A2 row 4 won’t update as row 3 did. Also cells AK3 and AL3 go wrong.
Any help on this will be appreciated.
I have attached a link to the workbook.
https://www.dropbox.com/s/d3ya2v7ls4...Scan.xlsm?dl=0

[jason.b75]

Please attach the file, not a link to it (see post 5, second paragraph).

[shina67]

Sorry Jason I hope I have managed to do the upload correctly.

[jason.b75]

I'm confused, there are no formulas in columns K-Z, which is what we were working on previously. Also, the code that I provided for you earlier is not in this file.
Have the requirements changed since, or is this an out of date copy?

The errors in AL3 and AK3 are being caused by the formula in AJ3 and your Worksheet_Change code copying the error from AJ3 to AI3.

[shina67]

I've mistakenly overwritten the previous version when my PC closed down excel unexpectedly.
This version is what I have managed to recover.

[jason.b75]

Taking another look, I think I can see an alternative way that will work, as opposed to repeatedly fixing one thing and breaking another

Can you confirm the pieces of data that will be entered manually each time, whether typed or by copy and paste from elsewhere.

I'm seeing columns G, H, I, J and AE for row 3 onwards.
A1 and A2 (quantity?) as generic to pass to the correct row (A2 into column AJ, matching row based in A1).
Are the quantites in A3 & A4 manual entries? I'm assuming that this is the initial order quantity?

Everything else is formula based working off of the above. Have I missed anything?

[shina67]

The data that will be manually entered each time is G,H,I,J. Possibly K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z.
I was hoping to work out that AE would pull the relevant info from Row 1 if there is a numerical value under K through to Z.
A£ & AE4 is the order qty

[jason.b75]

Ok, I think I have enough information to work with now. Will have a look at this later, but might not get it done today.

One point to confirm, order quantity in A3 would be a manual entry?

Working with formulas in the sheet, only one of the following will be possible, not both.
Enter the quantity in column K-Z based in what is entered in A1 and A2.
Enter the system in AE based on the number entered into K-Z.

I should be able to get around the either or scenario using vba to monitor data entry, but in the event that it doesn't work as intended, which of the above should I implement?

[shina67]

Hi Jason,

Essentially, I am trying to produce a scanning system that will trace whether a job has been completed or not and the remaining quantities still to do. I am a very small business and trying to pull us into the 21st century with possible future proofing. I have looked at various ERP systems but they are not within our budget.
The info gathered from another source is what is in columns G through to Z and this is generic. Therefore, columns G through to Z must have the same headers.
I have generated the barcode from the info in Job Number, Job Name & System (although system now is manually inputted, I would like to have this self-populate from the headers if there is a numeric value under it.)
Columns AB through to AG is what would be issued to the shop floor for them to scan into this workbook.
A1 is where the barcode scans into and A2 the quantity that has already been completed.
I was considering this scan being on an individual sheet so that all the info on sheet "Schedule" is not seen. The only info that would be seen on a separate scanning in sheet would be the info in columns AB, AC, AD, AE, AI & AM.
I think at the moment the sheet called “Labels” seems to be working correctly.

Thanks again for all your help with this.

[jason.b75]

I don't think the hidden columns, B, C, D, E, F and AH will be needed once I've finished.

Will removing them cause you any problems with anything else?

[shina67]

No they shouldn't. Not at work so cant check. But i dont think it will cause an issue

[shina67]

Hi Jason,

Columns, B, C, D, E, F, AH & AJ will not be needed for anything else apart from this sheet.

[shina67]

Hi Jason,

Not sure how far on with this you are but I have attached a spreadsheet with how I eventually envisage this workbook looking.
The barcode scan would be obviously done into the “Scan” tab. Which would then generate the data into “Schedule” columns G, H, I. Also, the data in “Labels” E1, A10, C10, G10 would generate from the barcode scan.
“Input Source Data Here” is the only thing that I would then have to copy & paste data into. This would be pasted directly under what is already in this tab.
Not sure if this helps with what you were asking or what you have already done. Please do not go out of your way to make changes to what you have already done. I really appreciate all your help already.

[jason.b75]

Hi, I haven't had a chance to look at the latest sample file that you've provided yet (just finished ironing out a few little bugs in the one below).
Take a look at this, see what you think. I've kept everyting in the 'Schedule' sheet for now, I find it easier to plan it when it is all in the same place, then it can easily be split to multiple sheets afterwards as and where needed.

This part doesn't include the 'Scan' entries in A1 and A2, yet, I've just added something to try and make the initial manual entry of G, H, I, J, K-Z, etc easier.

Click on a cell in any of the columns, K to Z and a form will pop up.
If you click in an empty row then the (current) date and system (based on heading of the column clicked) will be filled in, everything else will be blank.
If you click in a row with existing data, then Job number, Job name and Production stage will be copied from that row. Date and system will be as above.
Cancel on the form is self explanatory, Submit will add the data from the form to the next empty row on the sheet.

Could you give it a couple of test runs and see how it goes, if I've missed any columns that should be filled in at this stage, etc.

Currently it will not allow you to edit an existing row if you make a mistake, that is one thing that I will need to add to it later.

[shina67]

Hi Jason,

This would mean manually inputting every job instead of a simple copy and paste. Unless I'm doing something wrong.

[jason.b75]

Sorry, I made a last minute change to the file and forgot to test it again afterwards The data from the form was not being submitted back to the sheet.
In this one you will see an extra row of data has been entered, which I did with 1 click and 3 keystrokes. (Click on S4, type quantity, press Enter twice).

Will that work for what you need?

[shina67]

Thanks Jason,

That is brilliant work and would work for what I am requiring I think. My only issue is that there would be quite a bit of manual inputting for the 'schedules' instead of just a straight forward copy and paste from another source..
If you take a look at the 'template' file i have attached in post #35 you see what I have imagined as the final working workbook looking like.

[jason.b75]

I had a quick look at the file in post #35 earlier, but I was waiting on your opinion of the method that I was applying before continuing.

What information would you copy and paste in the schedules, that you think would require manual entry with this method? If you're thinking of the scanned labels, that is the next part of the task. Because this is quite involved, I'm getting one part right before moving on to the next.

With your 'Template' file, I would add the form to the 'Input Source Data Here' sheet, but anything submitted there would be appended to the 'Schedule' as well.

I'll copy the form to the template file in the morning and adjust the code so that you can see what I mean.

[shina67]

Thanks for your reply and help Jason.I will upload a sample of what is copied from another source in the morning.

[jason.b75]

I think I've been working in zero gravity, the proverbial penny has only just dropped

I was missing things in your replies (my fault, not yours) and thinking that you would be entering data manually into the 'Input source data here' sheet, or copying from rows above to create a new row for an existing job, rather than copying from another file.

Just to be sure I don't have another major brainfart.

Part 1. Initial data entry.
Copy data from external source and paste to new row on 'Input Source Data Here' (manual paste by user).
Create new rows in 'Scedule' based on the data entered above (to be automated).

Part 2. Scan.
Print labels based on 'Scan' of barcode. (Or should labels be printed in part 1 in line with qantity entered?).
Reduce outstanding quantity on schedule according to scan.

When you scan barcodes, does it have an automatic carriage return after each scan?
Will you scan each label, or 1 label then enter a quantity?

Part 3. Clean up.
Remove any rows with 0 outstanding from the Schedule. (Should a record be kept of what has been scanned and when?).

At the moment, it would be best to focus on Part 1, with any changes or additions that need to be made there before moving on to the next section.

edit:- Based on my assumptions, here's the file with part 1 done. Schedule and input sheets are both empty. I've moved the original data from the input sheet to the 'Copy Me!' sheet to aid with testing.

You can copy and paste 1 row or 1000 rows at once, but the range that you copy must be 20 columns wide to match the Input sheet. The code will ignore anything else. (See notes and colour coding in copy sheet for examples).

[shina67]

Hi Jason,

Part 1. Initial data entry.
Copy data from external source and paste to new row on 'Input Source Data Here' (manual paste by user). Correct
Create new rows in 'Scedule' based on the data entered above (to be automated). Correct

Part 2. Scan.
Print labels based on 'Scan' of barcode. (Or should labels be printed in part 1 in line with qantity entered?). Print labels based on 'Scan' of barcode.
Reduce outstanding quantity on schedule according to scan.Correct

Will you scan each label, or 1 label then enter a quantity? Each product will be scanned as a certain quantity is produced. I.E. a product with 4 on it may be produced over 2 days so therefore I need to scan what is produced when it is produced.

Part 3. Clean up.
Remove any rows with 0 outstanding from the Schedule. (Should a record be kept of what has been scanned and when?). A record of what is scanned and when is required.

You can copy and paste 1 row or 1000 rows at once, but the range that you copy must be 20 columns wide to match the Input sheet. The code will ignore anything else. The copy & paste is 20 columns wide

[shina67]

Hi Jason,

I have just tried running the file you attached.
I have encountered that when I copy & paste the following I am having a problem.
Capture.JPG
As you will see there is a quantity for Smart Windows & a quantity for Fixed Frames.
Previously I was copying this data twice and deleting the quantity for smart windows on 1 row and then the quantity for Fixed Frames on the next row.
Is this something that will cause a problem.
Can the sheet be altered so I can do the above or can it be altered so it automatically separates this into 2 rows. EDIT. I have sorted a way around it. If I copy & paste into 'Copy me!' and divide the jobs into sub-categories as in the system types I can then copy and paste that into the 'Input Source Data Here' tab. Although if it can be automated it would be better Also in the 'Schedule' tab where the barcode is generated is it possible for the number to be to zero decimal points?

[shina67]

Hi Jason,

I have been thinking some more on this as I was testing what has already been achieved as in Part 1.
As regarding Part 2. Is it possible to scan a stage within production as to where the job is? I have attached the last file you uploaded with the alterations I am referring to.
You will see on ‘Scan’ I have added a “Scan Stage Barcode Into Here”. If I was to scan a Product Barcode in, a quantity and a stage would it be possible to update the new columns in ‘Schedule’ as to whether the job is complete at each stage in production instead of just a “Outstanding Quantity”.

[jason.b75]

Looking at your comments in post #44, do you need it split into multiple rows on the Input sheet, or would it be ok on just the schedule with the input sheet staying the same as your screen capture?

[shina67]

Hi,

Yes it would it be OK on just the schedule with the input sheet staying the same as on the screen capture.
I have now acquired a handheld barcode scanner to scan into the sheet.
When testing the sheet with the scanner it will not pick up a barcode that has a space or characters somewhere within it. I.E: If the Job Name is " Barry Eckman" or "Barry O'Leary" or "Barry Eckman (SB)" can you change the coding that generates the barcode to not include these. So that the barcode would read something like "*25416BarryEckmanXPVIEW.*"" or "25416BarryOLearyXPVIEW." or "25416BarryEckmanSBXPVIEW."

[jason.b75]

Sure, that is a simple enough change. Will your barcode scanner pick up the "." at the end, or does that need to be removed as well?

[shina67]

It picks up the full stop but for standardising it could be removed.

[jason.b75]

This should take care of this issue in post #44 (except for the 0 decimal point formatting, as far as I can see everything is formatted to 0dp anyway).

Also added a bit to remove any non alpha-numeric characters from the barcodes.

Going to take a quick look at the new format now, but probably not going to get much done with it today.

edit:-

Just had a look, is there any connection between the various stages in the source data and the stages in the schedule, or should all go into columns E and L until something is scanned?

I assume that the columns are in order of events, left to right?

[shina67]

Is there any connection between the various stages in the source data and the stages in the schedule? No the various stages that are in the source data can be removed

I assume that the columns are in order of events, left to right? That's correct

[jason.b75]

After re-assessing what you asked, I realised that order of events doesn't really bear relevance to the task (monday brainfart again). I've left the stages in the source data for now, just in case removing them breaks something else.

This is an update on the file from my last reply, with the bits you asked for in posts 44 and 47 done.
Also added a load of code to the scan sheet, I've cut it down to one scanning box (I found it provided a safer way of tracking what has been scanned).
The code locks you to the scanning cell, it will not allow anything else to be selected in that sheet.
The box will ask for a Product barcode first, if you scan one that is not found in the schedule then it will warn that it is invalid and ask you to scan again.
Once a valid product is scanned, it will ask for a quantity, then a stage, following the same rule that it will keep asking until a valid barcode of the correct type is scanned.

After all 3 are scanned, it will update the stage columns in the schedule (outstanding quantity will be updated in stage scanned is *Complete*) and add a new row to the 'Scan Audit' sheet, detailing what was scanned and when.

I've edited the barcodes in the schedule sheet to remove all spaces and special characters (including the dot at the end) so that it will be uniform going forward.

I think that should be everything now except the labels. That is a task for tomorrow. Wanted to get this one uploaded tonight so that you will have some time to test it.

Regards
Jason

[shina67]

Hi Jason,

Thanks for that I will test it later on when I get into work and let you know how it went. Everything looks great on the latest file you attached. Liking the 'Scan Audit' sheet, I don't think it needs the barcode within that sheet though as it would only be for tracing purposes.

[shina67]

Hi Jason,

When I have copied & pasted the 'Input Source Data Here' it has copied the Outstanding Quantities into the 'Quantity Completed At Routing' column and not the 'Outstanding Quantity' column.
Also when I try and scan a product barcode it is telling me "Product Not Found Please Scan A Valid Product Code" even though the barcode is in the 'Schedule' sheet.

Edit: I have sorted the issue for Outstanding Quantities by changing .Cells(1, 12) = c from .Cells(1,9) = c in the Worksheet Change module

[jason.b75]

Is the barcode that you are scanning an exact match for the one on the Schedule sheet?
Remember that I've removed all of the spaces and dots from the list, so an old barcode may not match.

Only other thing that is coming to mind. Try scanning the barcode some into a blank sheet, then change the format to a normal font. Are the asterisks still there, or does the scanner drop them?

[shina67]

Is the barcode that you are scanning an exact match for the one on the Schedule sheet? The barcode is copied from the 'Schedule' so is an exact match.
Only other thing that is coming to mind. Try scanning the barcode some into a blank sheet, then change the format to a normal font. Are the asterisks still there, or does the scanner drop them?The scanner drops the asterisks. The asterisks is a marker to let the scanner know the start & end of the code.

[jason.b75]

Revised version, I've fixed the asterisk issue when scanning, corrected the column error from post #54 and removed the barcode from the scan audit sheet.
I think that I've made all of the necessary changes to the code whre it would be affected by these alterations.

[shina67]

Thanks for that Jason.
You did it a different way to me, I changed the xlwhole to xlpart.
Is there a way to stop the quantity scanned exceeding the quantity on the schedule against the product type.
Now it is Part 2. Scan.
Print labels based on 'Scan' of barcode.
When this is done is it possible to make a provision to print more labels to the product type even when the job is complete. This is to cover for potentially any labels being misplaced or the printer screwing up.

[jason.b75]

You did it a different way to me, I changed the xlwhole to xlpart.

That would disable the barcode validation
With xlpart, a quantiny barcode scanned instead of a product barcode would be accepted in error if that quantity was found anywhere in any product barcode in the list.
As an example, a quaintity barcode of 2, 5, 6, 7 or 25 scanned when it was expecting a product barcode, would match up against the very first entry in the Schedule sheet because they all make up 'xlpart' of that product barcode.

Scanning in excess of the schedule quantity is something that I had in mind, but hadn't yet given much thought on method.

For extra lables, how about a *Reprint* stage barcode that would skip out updating the schedule and audit screens.

Looking at row 13 of the Label sheet, does anything need to go in the gaps, or is that part for completion after it is printed?

Do labels need to be printed after every scan, or only at specific stages?

[shina67]

Scanning in excess of the schedule quantity would be ideal.

*Reprint* stage barcode that would skip out updating the schedule and audit screens would probably do the job.

Row 13 of the Label sheet is for completion after it is printed. EDIT:- The parts where at the moment #REF! & the barcode are the only parts that need updating according to what is scanned as *complete*

Labels only need to be printed after every complete scan with an option of how many is needed.EDIT:- irrelevant of the quantities scanned in

[shina67]

Hi Jason,

I have encountered another problem whilst testing this morning.
The barcodes all scan as they should returning the correct info apart from the *01* which is returning 10 every time into the 'Schedule' & 'Scan Audit' sheets.
Is this something within in the coding? . I doubt that it is as everything else is correct.
Can a provision be put in place that if *01* is scanned it returns a 1 in the relevant sheets. Maybe a divide by 10?

[jason.b75]

Just fixed the problem with 1 scanning as 10, it was due to the scan being interpreted as *1* with the asterisks being evaluated as wildcard characters, effectively overriding with xlwhole and evaluating it as a valid part match to 10.

Although the problem should only affect that 1 barcode, I've applied a fix to all barcode types to eliminate the possibility of it happening again.

It might take a couple of days before I get the next version of the file uploaded with the final touches applied, free time seems a little bit more limited than normal at the moment

[shina67]

Thanks Jason. Could you upload it so i can test it please

[jason.b75]

Sure, I don't think that anything else I've done will cause any issues. I've been making some changes towards the things you asked for in post #58, so if anything else trips up it could be related to some of that being unfinished.

Scan order has changed to Product, Stage, then Qauntity last. This is in order to stop the scan exceding the quantity on the schedule. By asking for Stage first, the quantity can be checked and rejected if it is too high.

Something that I forgot to tell you before. There is a hidden option to reset what you have scanned if you scan an incorrect Product or Stage barcode. Simply changing to another sheet, then returning to the Scan sheet will clear what has been scanned and reset to the Product scan stage without logging anything in the Schedule or Audit sheets.

Once all 3 barcodes have been scanned, it is submitted to both sheets so will not be reset.

[shina67]

Thanks Jason for that. I will run some tests tomorrow.I had already picked up on the reset option.

It might take a couple of days before I get the next version of the file uploaded with the final touches applied, free time seems a little bit more limited than normal at the moment.

I understand that you have your own things to do. I was being hopeful that it would be complete by Friday. But I get the lack of free time.

[jason.b75]

Things have gone better than expected, I thought that the fine tuning would take longer than it has.
New bits on this version:-
-Added scan limit. You will not be able to scan a quantity greater than either column E, or the previous stage (whichever is less). i.e. you can't scan 10 as cut if only 9 have been taped. If you try, it will tell you the maximum you can scan and ask you to scan again.
-If you scan a product and stage combination where the maximum available to scan is 0 then a message will warn you and the sheet will reset to the 'Scan Product' state.
-Labels sheet now updates when a valid 'Complete' stage is scanned.
-Added print function to the 'Complete' stage, following the above rules, but with your original print code added, giving the input box to specify the number of labels.
-Added *Reprint* stage. Scan Product, then Reprint, the quantity will be bypasses and will go straight to the print input box.
-Complete and print will update Schedule and Audit. Reprint will not leave a trail.

If you need to add more Quantity barcodes to the sheet, keep them in rows 1:15, anything below that will not be recognised.
In hindsight, I should have used a test for a valid numeric value instead of validating the individual barcodes, that is on my list of things to do with fixing any issues that you find in testing.

Hopefully there won't be too many of them

[shina67]

Thanks a lot Jason. Sounds like you have been busy working on the final stages of this.
I will test run it today and feedback to you.
Once again all your help is very much appreciated.
There are going to be some massive Rep's coming your way.

[shina67]

Hi Jason,

After a 1st initial trial I have picked up on the following, hopefully there are all minor tweaks. I have uploaded the template you sent last night with new source data in it so it's easier to see:-

1. On the 'Schedule' sheet you will see in F4, F188, F192, F202 etc. that the job number is pulling the digit after the decimal point. Can this be altered to exclude any digits after a decimal point?
2. The 'Scan Audit' sheet is also pulling the decimal point digit over. I am assuming if point 1 is sorted out that this will self correct.
3. Can the format of the barcode in 'Schedule' be increased to font size 72?I have now sorted this.
4. I have reformatted the 'Labels' by merging a few more cells so that the job name, system & Barcode are more visible when actually printed. I hope this won't screw with your coding for the labels? I have now sorted this.
5. Labels are defaulting to a normal print. I need them to print in Landscape, Colour and from tray 2. They are at present defaulting to Portrait, Grey Scale and tray 1.I have now sorted this.
6. Can on the labels the system be defaulted back to the same as in the headers on the 'Input Source Data Here' sheet?
7. I forgot to mention previously that I have had to alter the barcodes for single digit quantities as the scanner would not pick them up i.e. *1* now is *01*, *2* is now *02* etc. This has had a knock on with the quantity scanning in 'Scan'. I am assuming that you have linked the 'Quantity & Stage Barcodes' to the scanning as it is now telling me that it is an invalid quantity for any single digits. .

Sounds like a lot.

[shina67]

Hi Jason,

On further testing the following problems have occurred:-

1. When scanning a job and it populates into the various stages in the 'Schedule', the facility that you put in so that something couldn't be scanned at a stage unless it had been previously scanned at the stages before isn't working correctly.
See screenshot Schedule Capture.JPG'Schedule Capture'. There are 22 scanned into Cutting & Completed but only 11 scanned into Taping, Routing & Crimping.

[jason.b75]

Hi

Going back to your previous post, points 1 and 2 are easily taken care of, I've added that into the copy that I'm working on. You were right when you said that fixing it at point 1 would also fix point 2. (I think that you did ask for this to be done in an earlier post but I didn't follow what you were asking for because there were no decimals in the data that I was working with at that point).

Point 3, the barcode column in the last file you attached is a mix of font sizes 48 and 60. I've set the template pattern to 72 so everthing from here on will be that size.
For reference, if you need to change any formatting on the Schedule sheet, it needs to be done on row 1 (Hidden). This is what the code uses as a reference for the formatting on the rest of the sheet.

Point 4, I couldn't see any difference so not sure if you have attached the wrong version of the file?

Point 5, If you need the print code to be modified, it would be best if you could capture it with the macro recorder while running a manual print.

Point 6, I'm completely lost there.

Point 7, I'll change the code so that it will recognise any number rather than checking for a barcode.

For the subsequent problem with things not lining up, I'll go through the code again, as far as I could see last night it was all lined up as it should be. I might need you to attach a copy with misaligned data so that I can dig through and compare it row by row.

[shina67]

Hi,

Point 3. You will see in post #68 I had sorted but if you have written it into the code all the better.
Point 4. You will see in post #68 I had sorted.
Point 5. You will see in post #68 I had sorted.
Point 6. I want the info under the header "System:" to say SMART WINDOWS instead of SMARTWINDOWS etc.

[jason.b75]

Ok, I think I've fixed everything. Only problem now, I can't find the cause of the scanning issues that you mentioned in post #69.

I'm going over the code again before posting an updated file, to see if I can find a point where it could be comparing the current scan to the previous one.

On a different note, now that we have dropped the decimals from the job numbers, is it possible that there could be 2 or more records in the schedule with identical product barcodes?

If so how should it be determined which is the correct one to apply the scan to?

[shina67]

On a different note, now that we have dropped the decimals from the job numbers, is it possible that there could be 2 or more records in the schedule with identical product barcodes?

The decimal is only for sales before it gets to production. Once in production the decimal is dropped hence why I needed it dropped at this point.

[jason.b75]

See how this one performs, I think I've addressed all of the concerns raised.

I've added some extra bits to the code to create a debugging log, this will need to be removed before you go from testing to actual as it will most likely bloat the file and degrade performance over time. This is in place to track events so that if you get any more problems like those in post #69, I can see what caused it.

I notice that some rows on the Input sheet had zero values in a few columns, these were creating records in the schedule so I've tweaked the code to ignore them now.

I've deleted the rows from the bottom of the Schedule that matched up to the data on the Input sheet, then reapplied them to test the changes to the code.
Made a slight change to the calculation method that was in place to prevent scanning a quantity in excess of the previous stage.

Do you want anything in place to remove rows from the Schedule once they have a zero quantity outstanding?
I was going to automate this, but thought better of it in case you need the row retained for reprinting after it hits 0.
I do have a theory in mind to delete it as soon as it hits zero, with subsequent scans going straight to reprint, if that will work for you.

[shina67]

Hi Jason,

Thanks for that .

Do you want anything in place to remove rows from the Schedule once they have a zero quantity outstanding?
No I would prefer them left in at the moment. I will have a think about it especially if you can still do reprints as you have said. Will the scan audit remain the same if it is deleted from the schedule?

[shina67]

Do you want anything in place to remove rows from the Schedule once they have a zero quantity outstanding?
I was going to automate this, but thought better of it in case you need the row retained for reprinting after it hits 0.
I do have a theory in mind to delete it as soon as it hits zero, with subsequent scans going straight to reprint, if that will work for you.

After thinking about this overnight I think it would be a good idea to do that so long as the record remains in the 'Scan Audit' for trace ability purposes

[shina67]

Hi Jason,
6. Can on the labels the system be defaulted back to the same as in the headers on the 'Input Source Data Here' sheet?

I see that you have sorted this problem as well
All seems to be working OK.
The very last thing I can think of now is:-

After thinking about this overnight I think it would be a good idea to do that so long as the record remains in the 'Scan Audit' for trace ability purposes

Do you want the file uploading so you can check the 'Debug' log?

[shina67]

Can a =Trim() and =Proper() be inserted for the job name? in either the 'Schedule' or 'Input Source Data Here' sheets.

Where you have the following in the coding (Range("D3") = "Schedule Updated" & vbCrLf & "Scan Next Product Barcode") is it possible for it to also say after "Schedule Updated", "Remaining to Tape, Cut, Router, Crimp, Complete" (which ever stage has been scanned) then the quantity based on the total job quantity subtract the amount scanned at that stage?

[jason.b75]

As far as deleting 0 outstanding rows from the Schedule goes, yes they would still remain in the Audit sheet. Nothing will be removed from there. In the event that you need to 'undo' the last valid scan for any reason, you would need to edit the changes made by that scan manually in both the Audit and Schedule sheets.

As a backup, I can add in a yes / no popup box with a message on the lines of 'Job No / name / system is now complete. Would you like to remove this from the Schedule?' after the final labels have been sent to the printer, so that the option is there to remove or keep it.

Also, I think it might be a good idea to add 2 extra columns to the Audit sheet with the original order quantity from colimn E of the schedule and the product barcode (will be needed for reprinting labels after row is deleted from schedule).

The rest of the things that you've asked for are fairly straightforward. The information needed for 'Remaining to (stage) (quantity)' is already there, it just needs to be added to the existing message.

Finally, I don't need the debug file as long as you've had no issues. The problems that you mentioned in post #69 appeared to be down to the code not 'forgetting' the previous scan when it should. If you get another error, this would tell me what it is still remembering from the previous scan.

The main points to look at there if yo want to check it yourself for any issues.
With a product barcode in column D, A and B should be empty, C should be 0
With a stage in column D, A should show the location of the product barcode in the Schedule.
With a Quantity in column D, A as above, B should show the Stage, C should show the quantity remaing to scan for the stage.
Column E should show 0 in most rows, 'Schedule updated' rows should show 2, 3, 4 etc. As should the last 2 rows for any Complete stage (these 2 rows are where the labels print).
Column F should show TRUE for the 'Complete' stage rows with a number other than 0 in column E. FALSE for everything else.
Golumn G should show TRUE when column B says *Reprint* or D is 'Schedule not updated' False for everything else.
Column H alternates between TRUE (start of code cycle) and FALSE (end of code cycle).

I'll try and get a test copy with the final changes done later today, just need you to confirm the zero outstanding deletion based on the points above.

[shina67]

As far as deleting 0 outstanding rows from the Schedule goes, yes they would still remain in the Audit sheet. Nothing will be removed from there.
As a backup, I can add in a yes / no popup box with a message on the lines of 'Job No / name / system is now complete. Would you like to remove this from the Schedule?' after the final labels have been sent to the printer, so that the option is there to remove or keep it.

Ideal solution.

The information needed for 'Remaining to (stage) (quantity)' is already there, it just needs to be added to the existing message.

Brilliant.

Also, I think it might be a good idea to add 2 extra columns to the Audit sheet with the original order quantity from colimn E of the schedule and the product barcode (will be needed for reprinting labels after row is deleted from schedule).

If these are needed then they are needed. Won't make much difference to be able to read the info on the 'scan audit' sheet.

Thanks Jason. I finish work at 3.30pm so wouldn't get to look at any upload from yourself until Tuesday morning as I am on leave on Monday.

[jason.b75]

In that case, I'll hold back on posting a rushed copy in favour of testing a little more first. Will be uploaded ready for you to test on tueday for definite, probably sooner.

[shina67]

Jason you are a superstar

[shina67]

Hi Jason,

Sorry to ask for more but .......
Can coding be added to pull a .pdf file from "C:\Users\s.hayes\Desktop\Test Scan\" based on the job No or, job No and customer name.

I have tried something along the lines of the following but it is not working:-

Please Login or Register  to view this content.

[jason.b75]

I'm not sure on that one. As you have already started another thread on that, it would be best to keep is there, separate from this thread. Hopefully somebody else with better knowledge of the task will pick up on it.

Going back to the task at hand, did you want the "Remaining to complete" message in the scan cell, or as a pop up message box?

[shina67]

A pop up please

[jason.b75]

Take a look at these, both are essentially the same. The 'Debug' version has the same logging code as the last version, the other one has it removed so runs as a final version.

With the non-Debug file, I deleted everything from the Schedule, then refilled it from the Input sheet to check that the trim and proper case was doing as expected. The only thing it will not do is capialise irregular names such as those starting with Mc or Mac, or acronyms that are not delimited.

Any product found in the Audit, but not the Schedule will permit Reprint only, it will ask specifically for the Reprint barcode to be scanned, anything else will reject as 0 to scan.
A quantity of 1 or more must be scanned as Complete before reprinting will be permitted, on the theory that a label should not need to be reprinted before it has been printed.
Scans of 'Complete' stage will enter 2 rows into the Audit sheet, the first will show the stage, 'Complete' along with the quantity scanned. The second will show the stage, 'Labels' along with the quantity printed.
Reprints will log a single row as 'Reprint' and quantity printed.

Popup message boxes added at various stages, along with zero outstanding row deletion.

I've added the extra columns to the end of the Audit sheet, they can be reordered easily if needed, but the code will need to be changed to match the new order.

Think that covers everything.

[shina67]

Hi Jason,

Thanks for all your help with this project.

If I want to remove the pop up box asking "Would you like to remove this job from the schedule?"
Would it be as simple as altering the code to the following:-

from

[jason.b75]

Do you mean that you want to delete without asking?

If so, then just cut it down to

Please Login or Register  to view this content.

Remember to delete the 'End If' line as well or the code will error out.

Another option would be to use 2 lines so that you can easily switch between message and no message if required.

Please Login or Register  to view this content.

Note that there is an apostrophe in front of the first If, which means that it is only a comment, not a command, this will automatically delete without the message. By deleting that apostrophe and entering it in front of the second If, the message is reinstated.

If you use this method, one, but not both must be commented out with the apostrophe.

[shina67]

Hi Jason,

Everything seems to be absolutely fantastic thanks.

The sales team are looking at expanding and starting to sell unbranded products.
This would mean that the labels that print would be slightly different to the ones already in the workbook.
Is it possible to add a pop up box at the printing stage to ask if "Unbranded" labels are required?
If 'Unbranded' is needed then it would print labels from a new tab that I have added named 'Unbranded Labels'. The format as in rows and columns are the same. The data would populate to identical cells as in 'Labels'.
Obviously if the answer to the above pop up is 'NO' then it would print the labels from the 'Label' sheet that already exists.

[jason.b75]

I think that this should do it, there is no easy way to define your own buttons on a message box, that would need a userform, so I've gone with the closest option that I could think of.

First, in Module 1, remove the line

Please Login or Register  to view this content.

from the top of the PrintCurrentSheet code.
Next, in the code for the Scan sheet, near to the bottom, you will see the lines

Please Login or Register  to view this content.

Change those 2 lines to

Please Login or Register  to view this content.

I think it should do what you need, but have only done one quick test cycle.

[shina67]

Hi Jason,

I have copied the above and applied to the coding.
The option of "Unbranded" or "Branded" is now available but it is printing the 'Scan' sheet and not the labels

[jason.b75]

oops, the last line was missing

Please Login or Register  to view this content.

[shina67]

Now for some reason I am getting the following error:-
Attachment 619463

[shina67]

Sorry my brainfart time.

I had with psheet twice

[jason.b75]

The image you attached wouldn't load. Glad you sorted it.

You might find that the code doesn't initialise now, if that is the case, run the 'Toggle' code manually and it should start up again.

[shina67]

All works great.

One more question in another sheet I have attached to the workbook I have something similar to the scan sheet.
How did you lock the sheet to just the box to scan into and how did you make the wording change back to "Scan Product Barcode"
This is the code I have in that sheet:-

[jason.b75]

edit:- Before I forget again, when you post code, please use the # icon for [Code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/Code] tags. When you use the <> icon for [Html] [/Html] tags it can sometimes cause issues if the code is misinterpreted by the board software.

On to your question,

It's a 2 part process, the first one sets the sheet to default when it is selected.

Please Login or Register  to view this content.

The second one creates a virtual lock on the cell, you can actually select any cell in the sheet, but as soon as the selection change is detected, it returns to the default cell.

Please Login or Register  to view this content.

Not sure what you're trying to do with your code, the syntax looks correct, but a little confusing, I don't understand why you have With for a single line below, or a For-Each loop for a single cell
I'm gueesing that there might be some expansion needed on the code once you get it working as needed, but what you have at the moment, I think can be achieved in a single line

Please Login or Register  to view this content.

Hopefully that will give you something to work with, I'm going to be offline for the rest of the day, will check to see how you got on with this in the morning.

[shina67]

Hi Jason,

I am assuming that it is a simple copy and paste the above code below the coding already in that worksheet?
So the code would look :-

Please Login or Register  to view this content.

[jason.b75]

Above, or below, the order is not critical in that respect. The main thing to be cautious of is that one doesn't conflict with another.

Tip number 1. Always make a copy before changing anything, that way, if it goes wrong, you have a 'last known good' to go back to.
Tip number 2. Always save before testing. If it gets stuck in a loop for any reason, excel might crash and lose any unsaved changes.

[shina67]

Hi Jason,

Is it possible that when something is scanned the facility to input someone's name for who has done that process.
I.E. when something is scanned as being 'Taped' and then it asks for 'Quantity' can it then ask for who has completed that process.
I have added some columns in the 'Scan Audit' sheet to explain more what I mean.
I have attached the file so you can see.

[jason.b75]

I haven't looked at the file yet, but that should be easy enough to do.
Will the users have barcodes for their names? If not, what would be your preferred entry method?

[shina67]

Will the users have barcodes for their names? If not, what would be your preferred entry method?

Manual entry with a keyboard at the moment but could possibly be that I barcode everyones name at a later stage.

[jason.b75]

Just had a quick glance at the new file. Should a completed scan go to the fabricated column on the audit sheet?
What about QC check, will that be an additional stage that needs adding in?

[shina67]

Hi Jason,

I think the best way is to have the scan as an EDIT:- alphanumeric (payroll No & Name) in barcode format.
Completed scan would go to the fabricated column for the name. Also the QC check would only need to be a name scan not a quantity scan.

[jason.b75]

Hi, I've been looking over this and it is not going to be as simple as I had hoped it would.
With the additions and changes that have already been done, we have already ended up with a plate of spaghetti code, I need to try and untangle it before adding anything else to it.

I'll make a start on it when I can, but I know that I'm not going to get much free time to spend on it over the next few days, so it might not be ready until next weekend.

[shina67]

Ok thanks Jason. I was hoping to go live with it this coming week. We are in the process of looking at tablets that can run the coding and have a usb port for the scanner. We are probably going to need at least 5 or 6 as that is how many areas of the factory will be using the workbook at any one time. I'm hoping that when the workbook is made to a shared workbook it won't throw up any problems .

[AliGW]

Administrative Note:

Please remember that those who help here do so voluntarily and of their own goodwill. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[jason.b75]

I'm hoping that when the workbook is made to a shared workbook it won't throw up any problems .

That's a curveball that I wasn't expecting. There has been nothing I've seen in your earlier posts that would have suggested this being a potential requirement. When you mentioned adding user ID, I was assuming that it would be a small number of users with access to a single pc.
The methods that I've used are all based on exclusive access, they are not suitable for a shared workbook.
There is significant risk of data being overwritten / lost, or that the code will encounter an error and stop working if 2 or more users try to submit data at the same time.
If it encounters a runtine error mid run then there are any number of possible knock-on errors that it could create (think back to the screen captures where the data was in the wrong rows).

[shina67]

Please do not try to put pressure of time on anyone here.

My apologies if it seemed or came across that way, it was not intentional. All the help that Jason has supplied is very much appreciated .

[AliGW]

I think you probably need to respond to Jason's last post.

[shina67]

Jason, if this is an issue then I will have to rethink about how the data is going to be scanned in. I will probably have to look at having a central pc that everyone will have to use. It only occurred to myself earlier about it potentially being a shared workbook

[jason.b75]

I've been looking at ways to try and make it work as a shared workbook. I'm not saying that it can't be done, but it's not something that I'm able to do.

If not with a shared workbook, then it might be possible with a multiple workbooks feeding into a central excel / access database. This is not a procudure that I am familiar with, but I believe it is something that should not be exceptionally difficult. If you want to explore such options, it might be worth enquiring in the commercial services area of the forum to see if somebody is able to do this within an acceptable budget.

I will add the user name entry into the current workbook for you if you want to stick with a single workbook on a central pc, and fix error, if any, but this will have to be the last modification.

I'm not going to get chance to do anything else to it until thursday at the earliest, give my comments above some thought and let me know if you want me to continue with the current method.

[shina67]

Thanks Jason for all your help with this project . I thibm think that the best option is a shared central pc. Therefore the workbook would not be a shared one.

[shina67]

Jason I have put into an auto save in the vba after all entries. This at the moment seems to have sorted the problem with it being shared/exclusive use.

[jason.b75]

Tried that before, it doesn't work well.

If by chance 2 copies try to save at the same time the code will crash and everything will go wrong.

Also, as you mentioned tablets before, excel on some (if not all) tablets / mobile devices has limited functionality. As far as I know, vba doesn't work on android devices. Not sure about ios / windows mobile.

[shina67]

as you mentioned tablets before, excel on some (if not all) tablets / mobile devices has limited functionality. As far as I know, vba doesn't work on android devices. Not sure about ios / windows mobile.

Hi Jason, there are some windows tablets that excel works on perfectly fine, or at least all the reviews say so. I suppose time will tell on that after trying to run on them.

[jason.b75]

Just working my way through the changes, a couple of things that I missed previously.

1a:- With the label print quantity, given that you have set up the label sheets to print 4 labels per page, should the print quantity be for labels or sheets?
1b:- If it is number of labels, should it be rounded up to the nearest multiple of 4? I don't think that you will easily be able to blank out the unused part to avoid waste. *Hiding the logo on the branded labels is not possible with a shared workbook*

2:- What is the rule that you follow to decide on the non-bank holiday dates that the factory is closed over christmas / new year? At a glance, I'm seeing:-
- Closed from 25 Dec to 1 Jan inclusive.
- Closed 2nd / 3rd Jan as applicable if new years day is on sat / sun.
- No 1 day working weeks at the beginning / end of the closure / shutdown period.

I'm trying to add the bank holidays into the code so that they are calculated as part of the initial source data to schedule process based on the completion date, in order to remove the formulas that you have prefilled in the sheets.

The 'bloat' from the formulas and formatting that you prefilled in various sheets has increased the file size significantly, (yours is 848kb, my copy without is only 188kb) which is why it is slow to save. A problem that will be amplified when you try to autosave the shared version of the file.

[shina67]

Hi Jason,

Sorry for the late reply . In answer to your questions:-
1a. The print quantity is for sheets.

1b. Hiding the logo on the branded labels not being possible shouldn't be a problem as there are 2 sheets, 1 for each (branded & unbranded).

2.Closed from 25 Dec to 1 Jan inclusive.
Closed 2nd / 3rd Jan as applicable if new years day is on sat / sun.
The 1 day week would be worked.

The autosaving hopefully will work for the shared workbook as I have managed to limit the amount of users to 5.

[jason.b75]

1b is no longer an issue, that was only a problem if the answer to 1a was labels instead of sheets.

For the christmas dates, looking at your answer above, why was is 24 dec 2018 down as closed?

I just have to iron out a few bugs from splitting the code, then finish of the bank holiday bit and I think it will be done. Should have a copy uploaded either late tonight, or tomorrow morning.

[shina67]

why was is 24 dec 2018 down as closed?

We are not closed but use the day for cleaning purposes. So for the sheet it is a non productive day.
There is no rush Jason. I am not back into work until Tuesday so wouldn't be able to test until then.

[jason.b75]

That makes sense now, would that be the same every year? Last working day before christmas = spring clean day?

I'm trying to allow for all closure / non productive days in the code in order to eleminate the need for formulas anywhere in the workbook (other than the pivot).

One other point, do you see any need to keep the input data once it has been transferred to the schedule? I've been looking at ways to reduce save time when it is shared, less data is one way so clearing the input data as soon as it is finished with would help towards this.

[jason.b75]

In addition to the above, looking at the Survey / QC sheet, what is the full process here?

Should the QC Inspection be carried out only when the entire order is complete, or after each stage (this appears to be a given with the format of the sheet, but I have been known to miss the obvious quite frequently).
Is it a case of scan the barcode to the Survey sheet, then Print the QC sheet with the barcode details entered?
How it the hyperlink related to the process?
When should the QC be logged in the audit sheet, when it is printed, when it has been completed, after each stage has been checked, or a combination of the 3?

[shina67]

Same every year and yes no need for the input data once transferred over to the schedule

[shina67]

the qc and survey have both been removed now, there are no need for them. The Qc check should come after the completed.

The hyperlink is removed as the survey sheet no longer exists.
The qc needs logging after the completion stage

[shina67]

Should the QC Inspection be carried out only when the entire order is complete, or after each stage?

The
Qc inspection should be carried out after the order complete stage. Both the qc check sheet and survey sheets have been removed now. The reason for this is to limit the amount of users for this workbook. I have limited it down to 5 users now so hopefully any potential problems with the shared workbook should be limited. Sorry for the short and direct replies to your previous 2 posts Jason. We are away at the moment and the WiFi is very very poor.

[jason.b75]

Sorry for the short and direct replies to your previous 2 posts Jason. We are away at the moment and the WiFi is very very poor.

No worries, I thought you might have been busy and had to a quick reply when you got chance.

I've removed the same sheets from the copy that I'm working with, do I need to add a QC stage barcode to the existing setup so that it can still be logged to the Audit sheet, or are you keeping it out of the file completely?

Is the pivot still required?

[shina67]

Hi Jason,
Yes a QC stage barcode will be required to be scanned after the complete stage. This will also need to be on the scan audit. The pivot is required so I can tell how much is done daily at each stage and what is left to be done at a glance.

[shina67]

Hi Jason,

I am back in work now and have full use of a proper pc again.
The workbook basically consists of the following sheets now:-
Scan, Schedule, Scan Audit, Labels, Unbranded Labels, Input Source Data,Quantity & Stage Bar codes, Pivot.
This is all that is required now.
The QC Checked By in the Scan Audit should be scanned in after the complete stage and populate over to the scan audit sheet for trace ability.

[jason.b75]

OK, after many test runs, I think (hope) that this is working correctly now.

First, the importiant bit. Please do not add any formulas, named ranges, or conditional formatting to anything.
In addition to this, do not apply formatting to empty rows for later use, the schedule and scan audit will be formatted automatically as new data is added.

Scan audit and schedule have been set up as structured tables to make the data dynamic for the pivot tables.

Raw source data sheet copied from previous version for testing the input data sheet, this sheet can be deleted once testing is complete.

Input data code has been updated to fill all of the extra columns that you added to the schedule. The formulas are gone.
Bank holiday sheet deleted. The code calculates closure from the working day on, or before 24 Dec until 1 Jan (or substitute Monday if applicable for year) inclusive, Good Friday, Easter Monday, 2 bank holiday Mondays in May and 1 in August. It doesn't allow for any 1 off bank holidays, that might be declared in the future for special events or occasions.

I've changed the user id section of the audit sheet to only use 1 column, each row of the audit is stage specific, so it doesn't need a column for each stage as well.

Barcodes split over 3 sheets to avoid conflicts in matching, you will need to delete the stage barcodes from the quantity barcode sheet. I was not able to do this as you have protected the sheet.

Order of events is set with quantity limits so that everything must be scanned in the correct order. Reprint cannot be scanned before Complete. QC inspection cannot be scanned until all have been scanned complete. QC inspection cannot be scanned twice (possible if reprint scanned after QC inspection).

No allowance made for removing cancelled orders from schedule. Do you need a *Cancelled* stage added? Manual deletion could mess things up.

Thinking back to the point where we truncated the job numbers, you could encounter errors if there are 2 jobs with the same product barcode in the schedule at the same time.
Also a job in the schedule with the same product barcode as one that has been completed will prevent label reprints or QC inspection scans.

It would be wise to keep a separate file to archive the Audit sheet so that it doesn't become too large. Save time will become noticable as the volume of data increases.
In this case, archived data should only be removed from the file once the QC inspection has been scanned, with the QC row being retained to validate future reprints if required.

The Quantity barcode sheet is not needed by the code for validation purposes, so could be moved to another file if desired. The Stage and User ID barcode sheets are required for validation.

If any Stage barcodes are changed, then the code will need editing to reflect this. The code will not be affected by changes any other barcode types.

I've re-arranged the pivottables to prevent overlapping, hopefully this isn't too bad, making things look good is definitely not one of my good points.

edit:- While fixing the last problem that I noticed, I realised that the pivottable slicers are moving and overlapping as the number of columns in the table changes. I've attached the file for testing and started a new list of things to fix with this as the current sole entry.

I'm going to be out most of the day, so probably won't be able to reply to any feedback until late.

[shina67]

No allowance made for removing cancelled orders from schedule. Do you need a *Cancelled* stage added? Manual deletion could mess things up.

This is a very valid point and not something I had thought of to be honest. It is probably a good idea to add in the *Cancelled* stage.

It would be wise to keep a separate file to archive the Audit sheet so that it doesn't become too large. Save time will become noticable as the volume of data increases.
In this case, archived data should only be removed from the file once the QC inspection has been scanned, with the QC row being retained to validate future reprints if required.

I did think about this and thought about archiving the workbook monthly.

The User ID barcode sheets are required for validation.

Is this sheet populated from the scan of the user barcode?

I have noticed on the 'Schedule' & 'Scan Audit' sheets that the columns with dates in are formatted as mm/dd/yyyy. I don't want to screw with things as you have said in your previous post

the schedule and scan audit will be formatted automatically as new data is added.

Could the job not be deleted from the 'Schedule' until the QC inspection has been done?

[jason.b75]

The User ID barcode sheet needs to be pre-filled with a list of valid barcodes, the same as the Stage barcodes.
This is so that the ID scanned / entered can be validated, to prevent a wrong barcode type scanned in error from being logged in the audit.

For archiving, maybe a bit more often than monthly, time will tell. I would say anything more than 1000 rows in the Audit means it should be more frequent.
I have something in mind, but need to give it some more thought before suggesting it.

I hadn't noticed the date formatting error. That's added to my list, along with the Cancelled stage.
Is there a cut off point for cancellation?

Could the job not be deleted from the 'Schedule' until the QC inspection has been done?

Is that in reference to the truncated job number issue? Personally, I think that deleting rows is leaving you vunerable to errors, what if you forget to replace it after deleting it?

It would be better if the job numer was not truncated in the product barcode (at any stage). This would allow each to be uniquely identified.

To be honest, the issue was something that I only thought about while I was typing my previous post. I'll give it some more thought to see if there is something that can be done to identify duplicate product codes at the scan stage without over-complicating things.

[shina67]

The User ID barcode sheet needs to be pre-filled.

I have attached a updated book with where I had got to with this . You will see that there is a tab name 'Employees'. this tab takes the names from a central HR file and converts to the barcode. Could this be incorporated?

For archiving, maybe a bit more often than monthly, time will tell. I would say anything more than 1000 rows in the Audit means it should be more frequent.
I have something in mind, but need to give it some more thought before suggesting it.

Sounds interesting.

Is there a cut off point for cancellation?

No there isn't. I have known a job being cancelled part way through production.

It would be better if the job number was not truncated in the product barcode (at any stage). This would allow each to be uniquely identified.
I'll give it some more thought to see if there is something that can be done to identify duplicate product codes at the scan stage without over-complicating things.

If there is something you could come up with then that sounds ideal.

[jason.b75]

Just thinking a bit out of the box, are you still using Excel 2016, or have you upgraded to 2019 / Office 365?

[shina67]

Im using office 365 on the pc. However not sure what will be on the tablets. From what i have seen most only go as far as 2016

[jason.b75]

Which dates are in mm/dd/yyyy format? I've had a look and as far as I can see, all dates are formatted as dd/mm/yyyy.

Bear in mind that you could be looking at a stale copy, the one that you attached to post #132 is definitely out of date, the most recent version is attached to post #129.

You will see that there is a single user ID barcode in the file which I used for testing. The format of the user barcodes is not critical as long as the sheet matched whatever they will scan.

Using a full name might result in some rather large barcode id cards to be scanned. Try this formula in C2 to generate a shorter barcode.

Please Login or Register  to view this content.

Referring back to some of my earlier comments about file 'bloat', in your Employees tab, you have 968 rows of formulas but only 98 rows of data. That means that 89% of the sheet is wasting system resources. Whilst it might seem trivial, a little waste here and there adds up to a lot of waste overall.

Afterthought, do your employees have Clocking / Access cards with barcodes on them already? If so can your scanner read them?

I'll start looking at the other bits later, but will have very little time to spend on it over the next few days.

[shina67]

Afterthought, do your employees have Clocking / Access cards with barcodes on them already? If so can your scanner read them?

I have just created clocking cards with their payroll & name on them in barcode format. The scanner reads them and clocks them in and out. I used payroll number & name in the barcode format as we have employees with the same name.

[jason.b75]

So you've duplicated the same barcodes in this file? I can work with that.

[shina67]

So you've duplicated the same barcodes in this file? I can work with that.

Thats correct

[shina67]

Are you still using Excel 2016, or have you upgraded to 2019 / Office 365?

Hi Jason,

I am using Office 365 MSO 64 bit

[shina67]

Which dates are in mm/dd/yyyy format? I've had a look and as far as I can see, all dates are formatted as dd/mm/yyyy.

Hi Jason,

I have been testing the latest version you have sent. The date formatting is on the 'Scan Audit' sheet.
After doing some more testing I have a query.
At the moment when the job is scanned as complete and the stage for qc inspection is the next stage.
The qc inspection does not ask how many has been qc'ed.
Is it possible that the qc inspection can be done the same way as the previous stages? i.e. that the job is still in the schedule until the full quantity has been qc inspected, also the quantity that has been inspected populated over to the scan audit sheet.

[jason.b75]

I can't figure out why the dates are only wrong for you, for me they are in the correct format.

The only thing that I can think of is that the regional settings on your pc are for the U.S. or somewhere that uses mm/dd/yyyy as the standard date format.

I'll change the QC inspection scan so that it follows the same rules as the rest of the stage scans. In post #104 you said

Also the QC check would only need to be a name scan not a quantity scan.

with that and some other follow up clarification, I took it that the QC inspection should be done one the entire order once complete.

Not going to get chance to do that today, will try and fit it in tomorrow morning.

[shina67]

My apolgises on that Jason. Originally I did think it would only need that.

[jason.b75]

I seem to remember that you said some of the sheets are matched to the format of those in other files.

Would adding a QC inspection column into the schedule sheet (next to the Completed stage column) be a problem there?

[shina67]

I seem to remember that you said some of the sheets are matched to the format of those in other files.

The only sheet that matches format in another file is the 'Input Source Data' sheet

[shina67]

Using a full name might result in some rather large barcode id cards to be scanned. Try this formula in C2 to generate a shorter barcode.

Please Login or Register  to view this content.

This formula works great but misses working correctly with some. It also does not remove non alpha-numeric characters.
e.g in column a:- 608 column b:- Liam Thornton
column a:- 603 column b:- Christopher Ryan Smith

these are just a couple of examples
I have tried the following code but it takes out the * at the beginning and end of the barcode

Please Login or Register  to view this content.

[shina67]

Hi Jason,

Just been having a read through the coding to try and familiarise myself with code I've not used before.
Would I be correct in saying that you have put in an auto refresh on the pivot sheet when it gets opened?
Would I also be correct in saying that you have put in an auto save when ever there is a new change in the workbook?

[jason.b75]

Going back over your last couple of posts, the formula suggestion for the user barcodes was before you said that you had already printed them off in the current format. What you have will work so no point in changing it unless you specifically want them to be shorter. Either way, it would be better to keep them all in the same form so that each person only needs 1 ID barcode.

You are correct that the pivot is on auto refresh, it will work whenever the sheet is activated.

I can't remember exactly where I put the auto save commands without looking through the code for them, I tried to keep it to the main points so that it wasn't trying to save too often, from what I can remember:-
The scan sheet saves at the beginning and end of the process, so when the product barcode is scanned, then again when schedule and audit have been updated at the end of the scan cycle.
The Input sheet saves after the data has been updated, I can't remember if this saves before running as well, if it doesn't now, it will in the next copy.

I haven't had chance to make much progress yet, but should be able to get it finished later today and upload by morning.

[shina67]

Thanks Jason

[jason.b75]

See how this holds up in testing.

Date formatting issue is fixed, I found 1 wrong reference in the code which was causing it, although for some reason it was still showing up correctly in the sheet for me, possibly a windows regional format setting. I've forced all dates into dd/mm/yyyy format as they are passed to the sheet so hopefully that will prevent any more problems there.

QC stage is now running under the same rules as the other stages, removal from schedule only happens after all QC checks have been scanned and logged in the audit sheet.

Cancelled stage also added, will reduce all outstanding quantity columns by the scanned quantity, so works for full or partial cancellations. (just realised that I didn't test it with the whole order cancelled after part is scanned as complete / QC checked, when the outstanding will turn negative).

User barcodes from your file copied over and written in directly without formulas. This gives you an up to date starting list, to add a new user to the list, simply scan their ID 3 times at the 'Scan user ID' part of the scanning process, then hit the 'Yes' button.

All barcode sheets hidden in the file, Quantity barcode sheet is not needed, but I've left it in the file in case you need it there for printing the barcodes.

Still giving the archiving and truncated job numbers some thought, but wanted to get this uploaded for you to test.

[jason.b75]

This one contains the results of some testing that I've just done with cancelled orders.

In the top row of the schedule, the whole order was cancelled after part of it had been completed at some stages. You will see that some of the 'remaining to complete' columns contain negative values, which is expected given that more have been produced at that stage than are now required.

How should these be logged now? Is the cancellation log in the audit as you would expect?

One of my thoughts was to prompt for the surplus to be allocated to the next job for the same system, but I'm not sure if I would be able to fit that into the existing code. Especially if it is a large surplus that needs to be allocated to more than 1 subsequent job, or if there is no job to allocate it to.

[shina67]

Hi Jason,

When an order is cancelled it can be totally deleted from the whole workbook as though it was never there in the first place. Probably the only trace left would be in the scan audit with a user id of who has deleted it. No need to reallocate anything already produced to another job.

[jason.b75]

When an order is cancelled it can be totally deleted from the whole workbook as though it was never there in the first place.

Should that include deleting the already produced items from the audit?

On a different note, thinking about the workbook being shared. I'm going to suggest (advise) changing the audit from a single sheet, to a sheet for each stage.
One of the main problems with sharing and a single sheet will be conflicts when 2 or more users are scanning at the same time, changing this will remove that problem unless 2 users try scanning the same stage at the same time.

Other than potential issues with feeding the data to the pivottable, can you see any reason not to use this method?

[shina67]

Should that include deleting the already produced items from the audit?

Yes

I'm going to suggest (advise) changing the audit from a single sheet, to a sheet for each stage.
Other than potential issues with feeding the data to the pivottable, can you see any reason not to use this method?

Seems ok to me.

[jason.b75]

Ok, I've found a couple of bugs in the last copy that I attached, I'll try and get the fixes and changes done and uploaded by the end of tomorrow so that you can test on monday. Failing that it will not be done until next weekend, I'm going to be away for a few days from early monday morning.

[jason.b75]

Still trying to get the cancelled order code working correctly.

If an order is to be reduced in quantity, should it be adjusted down in the sheet (partial cancellation) or would the whole order be cancelled and a new order raised for the lower quantity?

Not sure that I'm going to get it finished today, I still have a fair bit to do, not including testing.

[jason.b75]

Not done much testing, but think I've done everything that it needs.
Archiving, I've left for now until the file has been fully tested.
Also, I need to sort the pivot table out for you. When I tried to combine the stage audit sheets into one table the file size increased significantly and everything went wrong, so that is missing for now.

With duplicate products in the schedule, caused by the truncated rows, I've allowed for them best I can without making it too complicated.
The scan will be allocated to the first match found in the schedule for the product scanned (top to bottom) that has a quantity outstanding for the stage scanned.
If the quantity scanned is greater then the quantity in the schedule then the user will be given the option to search for a duplicate product in the schedule, or change the quantity.
I did a quick test with duplicate product barcodes in the schedule, but not triplicate so it could fail there.

Cancelled orders, if the whole order is cancelled then all trace will be removed from the file except for an audit log of the cancellation.

If a partial order is cancelled, then the schedule will be adjusted accordingly.
If the quantity already scanned for a stage is less than the part of the order not cancelled then the scan qauntity in the audit will not be changed.
If the quantity already scanned for a stage is greater than the part not cancelled then the audit records for each stage will be adjusted down (starting from the last entry) until the total is equal to the order. Rows with 0 remaining after adjustment are deleted from the audit, any row that is reduced, but not deleted will have a comment added to the quantity scanned to say that it has been changed.

In either case the total order quantity column will be adjusted in all audit sheets.

Also, see post # 149 for some additional notes regarding previous changes. Those notes still apply except for the bit about cancelled orders.

I'll be able to check the forum when I get chance over the next few days to see how your testing is going, but will not be able to work on the file until I get home.

[shina67]

Hi Jason,

Once again thanks for all your time and help on this .
Looking forward to testing all the changes you have made. Cancelled would not be reduced in quantity they would be cancelled and rebooked with the new quantities. Like the idea of scanning a user id 3 times to add them to the list and do away with formulas. I will update later today how an initial testing session has gone. Enjoy your few days away.

[shina67]

With duplicate products in the schedule, caused by the truncated rows, I've allowed for them best I can without making it too complicated.
The scan will be allocated to the first match found in the schedule for the product scanned (top to bottom) that has a quantity outstanding for the stage scanned.
If the quantity scanned is greater then the quantity in the schedule then the user will be given the option to search for a duplicate product in the schedule, or change the quantity.
I did a quick test with duplicate product barcodes in the schedule, but not triplicate so it could fail there.

I wouldn't worry to much on that Jason as the likelihood on there been a triplicate is very very low.

If a partial order is cancelled, then the schedule will be adjusted accordingly.
If the quantity already scanned for a stage is less than the part of the order not cancelled then the scan qauntity in the audit will not be changed.
If the quantity already scanned for a stage is greater than the part not cancelled then the audit records for each stage will be adjusted down (starting from the last entry) until the total is equal to the order. Rows with 0 remaining after adjustment are deleted from the audit, any row that is reduced, but not deleted will have a comment added to the quantity scanned to say that it has been changed.

In either case the total order quantity column will be adjusted in all audit sheets.

Partial order cancellation is not an issue Jason as the whole order would be cancelled and re-keyed on as a new order.

[shina67]

Hi Jason,

With more testing done I have encountered the a problem when reprinting labels.
The barcode at the top of the labels is correct. The Job No, Customer Name & System are returning incorrect info.

[jason.b75]

I wasn't sure about the partial cancellation, I think that one of your earlier replies implied that it would be the whole order anyway. I'll drop that from the code and add a check that any cancellation scan is equal to the original order quantity, with more or less being rejected as invalid.

As soon as I read your post about incorrect reprints I realised my error. The code to set up the labels is nested into the Complete stage, so reprints will always be the last product scanned as complete. When I was testing, I only used 1 product for the complete cycle so didn't notice the error.

I'll be back home late thursday, so will probably look at any corrections on friday.

[shina67]

Thanks Jason

[shina67]

Archiving, I've left for now until the file has been fully tested.

After some testing the file has increased in size by 50%. I think i am going to have to archive fortnightly. The file would get to big if left longer and then start lagging.

[jason.b75]

Updated version for you to test.

The incorrect label detail on reprints should be fixed. As far as I could see, it was only happening when you reprint a label for a product that has already been removed from the schedule.

I've changed the cancelled stage code so that it cancels the whole order (no quantity option given). It will ask for the product to be scanned again for confirmation after the cancelled satge barcode is scanned.

One issue with the cancelled stage (with this version and the previous one). Cancelling a product for a truncated job number will cause the first instance of that job number to be removed from the schedule and all instances from the audit sheets. There are methods that could be used as a workaround for this, but they are beyond what I would consider 'reasonably practical'.

What is your definition of archiving? Would you transfer the audit data from this file to another (deleting it from this one) or keep the whole file and start a new one to continue with?

edit:-

I don't think that you will be able to have your pivottables, or at least not in the same format. Using the data model to create one pivot from multiple tabs makes the file size to large to be practical.
In addition, after many attempts, I still haven't been anywhere close to a format that I would consider useful.

At best, you could have the pivot linked to the schedule the same as before, with a mini-table for each audit stage, but not one that covers all stages.

For the pivot to be in the same format that you had earlier, it would mean reverting back to a single audit sheet with an unshared workbook.

I'm still not convinced that what I've done is going to work once it is shared for multiple users, but I've done all that I can to try and make it work.

[shina67]

What is your definition of archiving? Would you transfer the audit data from this file to another (deleting it from this one) or keep the whole file and start a new one to continue with?.

Thanks Jason, will start testing one Tuesday on my return to work .
As far as archiving goes I was thinking that the scan audit sheets would only have to be archived. This would mean that any jobs that are still not fully completed would not have to be re-entered and then re-scanned through to the appropriate stages where they were. Unless you have a theory for this?

Wish I had half the knowledge you have with VBA.

[jason.b75]

Trying to dump data from the existing file to an archive could be a bit messy.

Instead of re-scanning everything, why not just copy and paste everything that is left in the schedule to the new file?
Might also need to cut and paste (not copy, that would duplicate records) some of the completed stage audit data to the new file for reprints of anything already cleared from the schedule.

The old file with the existing audit sheets then becomes the archive. VBA and non-audit sheets could be deleted if desired.

The only problem that I see with this is that cancelling a part archived product would still leave the scanned stages in the archive audit.

My vba knowledge is not that great, most of what I have done uses a small number of simple commands repeated many times. I expect somebody with good knowledge could do the same thing a lot more efficiently and with the parts that I have struggled with working flawlessly.

[shina67]

I was thinking along similar lines for the archiving but I was only thinking of cut and paste, not copy and paste. Is this going to be something that will require doing manually? Can it be coded?

[jason.b75]

I'm going to suggest manual to start with.

If the rest works when it is shared and running on the tablets then look into coding the archive process.

If the sharing doesn't work, then doing it now will mean more to change again later.

[shina67]

you could have the pivot linked to the schedule the same as before, with a mini-table for each audit stage, but not one that covers all stages.

I missed this bit that you posted. I was thinking about the pivots overnight and thought about doing it from the 'schedule ' sheet. With slicers for each stage

[jason.b75]

Bearing in mind that doing the completed products pivot from the schedule would only give you that which is partially completed, not anything that has been fully completed and removed from the schedule.

Also, your audit pivot had the rows set by date scanned, where as using the schedule would result in the rows by date due.

[shina67]

The pivot would give me what is outstanding at each stage to complete and a completion date. From the slicers I could see how many for each stage is in arrears for the completion date for that process. That could then determine future schedules if less needs to be scheduled or if overtime is needed to hit the required dates.

[jason.b75]

Now I follow. I was looking at the other pivot that you had in an earlier version which was summarising what had been done rather than what was still to do.

I'll leave it for you to add the pivots that you need. I would advise avoiding the data model though, unless I'm doing it wrong, it increases file size by around 300%

[shina67]

Hi Jason,

After some initial testing on the deletion option I have encountered the following:-

After asking for the product barcode to be re-scanned to confirm deletion and then the cancelled stage it is coming up with - "Incorrect barcode scanned. Please rescan product barcode to confirm deletion"
When I have ignored this message and scanned the User ID it has carried on and deleted the product.

Is this just the wording that needs changing that appears at that point?

[jason.b75]

I've just run a test on my copy and I'm not getting the same problem. If you scanned the 'Cancelled' stage barcode again then that is why.

For a cancelled order it should be:-

Scan product
Scan stage (cancelled)
Re-scan product to confirm
Scan user ID

There is no quantity scan, it automatically does the full order, hence the double scan of the product for confirmation. The stage does not need to be scanned again after the re-scan of the product.

[shina67]

Hi Jason,

I have retried with the way you have mentioned in post #173 and still doesn't work that way.
I have scanned in the following order for it to work:-
Scan product
Scan stage (cancelled)
Re-scan product to confirm
Re-scan stage (cancelled)
Scan user ID.

I have attached the file for you to cross reference.

[jason.b75]

Not quite how I mentioned.

The stage does not need to be scanned again after the re-scan of the product.

After you Re-scan the product, it shoud be asking for user ID, not stage.

[shina67]

Ok I have done it again.

I have had to do it how I explained in post #174

Scan product
Scan stage (cancelled)
Attachment 623266
Re-scan product to confirm
Attachment 623267
Re-scan stage (cancelled)
Scan user ID.

[jason.b75]

I'm guessing that you tried to attach screen captures to your last post, but the links are coming up as invalid.

After you Re-scan the product, is it asking you to scan stage or user ID?

I can't make it do what you are saying without entering an incorrect barcode.

[jason.b75]

Just to clarify, the exact steps that I used, which work fine in my orignial copy, and the copy that you attached to post #174. (Bearing in mind that I don't have a barcode scanner on my laptop).

Go to the schedule sheet and copy a product barcode (excluding the asterisks).

Paste the product barcode to the scan sheet.
Type in the stage 'cancelled'
Paste the product barcode again.
Type in user ID.

Job finished. No second scan / entry of 'cancelled' stage needed.

[shina67]

Go to the schedule sheet and copy a product barcode (excluding the asterisks).

Paste the product barcode to the scan sheet.
Type in the stage 'cancelled'
Paste the product barcode again.
Type in user ID.

Job finished. No second scan / entry of 'cancelled' stage needed.

Worked great using the way above.
When using the scanner I get to the scan product barcode the second time and then get the message that an incorrect barcode has been scanned.
At that point if I scan the cancelled stage and then a User ID it goes through to cancel and delete the order.
Seems very strange that it works with a manual input but when used with the scanner there is an issue.
This is the only issue that has arisen whilst using the scanner.

[jason.b75]

I'll go through the code and double check the scan points in case I've missed an asterisk substitution somewhere, although that probably won't be until tomorrow morning now.

[shina67]

Thanks Jason

[jason.b75]

I think that I've fixed the issue but not had time to test it.

Also, the fact that you were able to force it through by scanning the cancelled stage again was an error, which I think is now also fixed.

The changes that I've made all fall within the parameters of the cancelled stage so there should be no need to test other areas again.

[shina67]

Hi Jason,

I've retested the latest updated version you have uploaded and it is exactly the same

[shina67]

I have tried sharing the workbook and have had the following message appear:-

"This workbook cannot be shared because it contains Excel tables or XML maps. To share this workbook, convert the tables to ranges and remove the XML maps.

To convert a table to a range, select the table, and then on the Design tab, in the Tools group, click Convert to Range. To remove XMl maps, use the XML Source task pane (On the Developer tab, in the XML group, click the Source button). To display the Developer tab, click the File tab, click Options, click Customze ribbon, and then under the Main Tabs select the Developer check box."

[jason.b75]

I've added a message box for testing this time, when you rescan the product it will show what has been scanned (top line) and what is expected (bottom line).

Both lines should be identical, if they are not then that is the cause of the problem. As it works with copy and paste, but not scan, it would be a reasonable assumption that the issue is related to the scanner. I was thinking an error in the barcode, but that would be rejected on the initial scan as it wouldn't match up to the schedule.

[shina67]

I've added a message box for testing this time, when you rescan the product it will show what has been scanned (top line) and what is expected (bottom line).

Hi Jason,

I have tested the deletion stage now multiple times and it comes up the same as the attached pic. Attachment 624071
Every time it scans once in uppercase and then in lowercase. I don't know if it is case sensitive or not but this is the only thing I can see.

[jason.b75]

It seems strange that it would scan the same barcode in different cases, but at least now we know what is causing the problem, it can be fixed.

I'm in the process of sorting out the bits that are preventing you from sharing the workbook. I'll change the problem line with the cancel code so that it is not case sensitive while I'm at it.

[shina67]

It seems strange that it would scan the same barcode in different cases.

Exactly what I thought

I'm in the process of sorting out the bits that are preventing you from sharing the workbook.

Thanks Jason.
I don't suppose you have given the archiving anymore thought?
I thought maybe another sheet with a button in it to click to archive. A macro obviously attached to the button that would cut and paste the older stuff not needed from the sheets, open a new workbook & dump it all in there. Name the sheet the date its run and save it. All automated so anyone can run the archive at anytime.

[jason.b75]

Button and macro is the easiest way to do the archiving.

My thoughts are either a workbook for each year, or one for each month if the data volume is going to be quite high.
If the volume is low enough for one workbook per year, then a sheet for each month, otherwise a sheet for each week?

With regard to the date that the dump is allocated to, would you want it by audit date, or by 'production completion date' so that all records for the same job are in the same sheet?

Probably not going to get the fixes to the last file (cancelled barcode issue and sharing) finished until tomorrow, but will upload it later if I do manage to finish today.

[shina67]

My thoughts are either a workbook for each year, or one for each month if the data volume is going to be quite high.
If the volume is low enough for one workbook per year, then a sheet for each month, otherwise a sheet for each week?

I was thinking of a monthly workbook with a sheet for each week in it.

With regard to the date that the dump is allocated to, would you want it by audit date, or by 'production completion date' so that all records for the same job are in the same sheet?

I think for ease and clarity of being able to find a particular job by name or job No, then the best way would be by 'production completion date'.

[jason.b75]

I still haven't had time to get this finished, but this copy should be adequate for testing the cancellations and sharing.

Due to the removal of the tables for sharing, the formatting of the schedule and audit sheets is no longer automatic, so the column shading, borders, font size etc. will all be wrong.

I should be able to get that sorted by the end of the week, although, if you find any more issues when it comes to sharing the file then I'll address those at the same time so that might take a bit longer.

Also, time permitting, I'll add in a quick archiving routine for you to try out. I used something for one of my own projects a while back that should be suitable, hopefully it won't need too many changes.

[shina67]

Hi Jason,

After testing the latest version the only issue I can see is that the info transfered to the 'Cancelled' audit sheet is not correct.
What I mean by that is that in the Quantity column it is populating the user id instead of quantity.
Everything else seems to be ok and working great.
Capture.JPG

[jason.b75]

Having given the archive a little more thought, one statement that you made earlier is nagging at me, 'All automated so anyone can run the archive at anytime.'

The archiving process could be quite slow, especially later in the month as the data volume increases, or at any point where data is being archived to multiple months. I can't predict all potential problems without being able to test it in a theoretical 'live' environment, but I would expect issues if anyone tries scanning while the archive is running.

Personally, I would unshare the workbook at the start of the archive process and share it again at the end in order to ensure exclusive access to the file, however, this would mean that all users would need to close the scan file before running the archive, otherwise they would be disconnected from the file, resulting in multiple unshared copies.

I'll think it over through the day, but I'm not convinced that there is an ideal solution.

[shina67]

Hi Jason,

If it could be automated could it be scheduled to run in out of hours, say a Sunday ?

[jason.b75]

That can be done, but I'm not sure how reliable it would be. It would mean that you would need to leave a computer on with the workbook open over the weekend. If anything goes wrong, you could be coming in to corrupt data on monday morning.

Thinking about alternative methods, what are your normal business hours, start, finish and breaks (if everyone stops at the same time)?

Also, approximately how many products would be scanned at any single stage on a typical day? Would this be a steady flow of scans at consistent intervals, or in a batch followed by a large gap?

Finally, at what point would it no longer be necessary to reprint a label? (Given that the reprint is validated against the QC audit sheet once it is removed from the schedule, this might need to be archived at a lower frequency than the rest).

With the above, I'm looking at the option of each stage being archived at the end of a scan cycle based on number of records in the audit sheet for the stage, with allocated timeslots to prevent conflicts.

[shina67]

Thinking about alternative methods, what are your normal business hours, start, finish and breaks (if everyone stops at the same time)?

Working hours are 6am until 10pm, Mon-Fri & 6am until midday Saturday.

Approximately how many products would be scanned at any single stage on a typical day? Would this be a steady flow of scans at consistent intervals, or in a batch followed by a large gap?

At 'Taping' there would be approx 25-30 products scanned daily which would be batched scanned at approx 3pm-4pm. At 'Cut', 'Routed' & 'Crimped' it would be the same quantity but at approx. 6am-8am. At 'Complete' & 'QCInspection' it would be the same amount but at staggered intervals throughout the normal working hours.

At what point would it no longer be necessary to reprint a label?

The reprint option for a product would not be necessary a day after the 'QCInspection' stage.

I'm looking at the option of each stage being archived at the end of a scan cycle based on number of records in the audit sheet for the stage, with allocated timeslots to prevent conflicts.

Something I didn't think of is the possibility of staggered archiving. Brilliant idea.

[jason.b75]

Ok, let me think it over a bit more. With what you've said above, my original theory will not work, with 25-30 scans per day it would need a consistent scan rate of 1 scan every 30-40 minutes at every stage to make it reliable.

Given that the cancelled / reprint stages should be far less frequent, it might be necessary to attach the archiving of those to another stage (QC inspection?).

Do you think that the following archive schedule would work?
Taped:- Monday & Thursday (P.M.)
Cut:- Monday & Thursday (A.M.)
Routed:- Tuesday & Friday (A.M.)
Crimped:- Wednesday & Saturday (A.M.)
Complete:- Tuesday & Friday (P.M.)
QCInspected:- Wednesday (P.M.) & Saturday(after 10 A.M.)

With the above, assume A.M. refers to 6A.M. until 2P.M.

A counter would be used to prevent each individual scan from being archived when the allocated timeframe is active, this would most likely mean that the the archive will run with the first scan for each stage, the rest of the scans for that day will not be archived until next time.

Added thought, archiving might need to be cross checked against the schedule. If a product is archived before it is 100% complete, then it would not be cleared from the archive in the event of subsequent cancellation. This is getting a bit more complicated than I would like.

The code that I have in one of my own projects, which I thought that I might be able to use for the archive isn't going to work as well as I thought it would, so I'm going to have to start from scratch.

Added thought number 2, while I've got cancellation in mind. If you have 2 records for the same product in the sheet at the same time (original plus 1 with truncated job number) then cancelling the truncated job would result in the original order also being cancelled. This is something that can not be avoided with the truncated job numbers.

I've attached the latest version of the file, with the user name going to the quantity on the cancelled sheet fixed, and the formatting added in.

I don't think that I'm going to get much chance to work on the archiving for a few days.

[shina67]

Hi Jason,

My thoughts were if it's possible.
Could the archive not be ran after midday on a Saturday.
Cross check what has been 100% completed as in right through to the QC Inspection stage. Then the data that is at this stage being archived. The remainder of the data left in the original.
This would take away the issue of if a product is archived before it is 100% complete, then it would not be cleared from the archive in the event of subsequent cancellation.
The cancellation would basically be voided then as the job would have been deleted from the schedule as it would be classed as 100% complete.

If you have 2 records for the same product in the sheet at the same time (original plus 1 with truncated job number) then cancelling the truncated job would result in the original order also being cancelled. This is something that can not be avoided with the truncated job numbers. This should not be an issue.

[shina67]

Hi Jason,

I have shared the workbook via OneDrive as its more secure.
I have allowed a couple of areas use the workbook this morning to try and test with other users, so as to see what other problems, if any, may occur when I let it go live.
I have just had to sort an problem where a user has accidentally clicked onto the coding. Luckily it was an easy fix what they had done.
Going forward is there any way you can think of to stop this happening.
SOLVED, sorry had a panic moment

[jason.b75]

I think that I can work with that, not got a lot of spare time at the moment. I've added a quick test routine to the file here, if you type Archive into the scan field as a product barcode then the test will run through and change the font to a strikethough for anything that should be archived.

I also just noticed a date formatting error (product completion date in the audit sheets), so I've fixed that as well.

edit:-

How did your user end up in the code? Unless something goes wrong and they click 'debug', they shouldn't end up in the code unintentionally.

The code can be password protected to restrict access while still allowing it to run. Not sure how that would work with onedrive as the sharing location though.