Filling in Rows Coming From Predetermined Values

Good morning all,

I was hoping you could help me with something I am trying to achieve. Forgive me if I fail to explain concisely.
Capture.PNG

The above image shows a timetable I am trying to create for a production environment.

The "Part #", "WO #", and "WO QTY" fields are manually entered into this sheet. The other cells in the rows are all calculated automatically using a range of different formulae and calculations/functions.

The figure in the "Run Time Board/hour" "Target" field is automatically populated using a VLOOKUP from another worksheet. The information used is as below:

Capture2.PNG

This target is then pulled through from the information above.

However, as this is a time sheet/efficiency measure for a production environment, and as specified in the first imaged - the quantity of boards we are going to produce is more than 947. 1500 to be exact (taken from "WO QTY") field.

Is excel smart enough to be able to work when it should stop displaying figures on an hour by hour basis?

For example:
We are producing 1500 x SA1K0310
07:00 - 08:00 - The target output will be 947
08:00 - 09:00 - The target output should be 553 (not 947)

Is there a formula I can use for this?

Currently, the formula in the "Run Time Board/hour" "Target" field looks like this: "=IFERROR(VLOOKUP(D10,'SMT Timings'!A:F,6,FALSE),"")"

If I could call on the wisdom of the people on here to help me then I would be most appreciative.

Many thanks in advance.

It sounds like the quantity you want on subsequent rows for the same part number will be your WO QTY minus the Run-time targets already produced for that part number, so you would have your VLOOKUP formula minus a SUMIF function over previous rows.

It's difficult to give an exact formula without seeing how your data is arranged in the actual worksheets, so it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon (Attachments button) does not work on this forum.

Hope this helps.

Pete

Hi Pete,

Many thanks for your response. I have attached the spreadsheet as requested.

Best regards,
Reece

This is the formula I have come up with for cell O10:

=IF(D10="","",IF(COUNTIF(D$10:D10,D10)=1,MIN(H10,IFERROR(VLOOKUP(D10,'SMT Timings'!A:F,6,FALSE),"")),MIN(VLOOKUP(D10,$D$10:$H10,5,0)-SUMIF(D9:D$10,D10,O9:O$10),VLOOKUP(D10,'SMT Timings'!A:F,6,FALSE))))

and this can be copied into O12, O14 etc. You can then copy column O to the other daily sheets.

I've added some test data into the Monday sheet so you can play about with it to check that it is working okay. I have assumed that if you need to make more than one-hour's worth of a particular PCB, then you would just add the Part No to other cells in column D, but that the total number for that Part will always occur on the first row that it appears (doesn't matter if it is reproduced below, and same with the WO #). The formula will cope with 2 or more entries for the same PCB, as can be seen in the attached file. The formula will also cope if you don't want to make a full hour's-worth - try changing H10 to 500, or H16 to 100.

Hope this helps.

Pete

Pete,

That is fantastic. Thanks for your help. Without being a pain in the proverbial, could you possibly explain the breakdown of the formula to me? Hopefuyll I can then understand it and use it myself again in the future?

Furthermore, one final thing to ask: If I only want to produce 50 of a specific PCB, is there a way to keep the same calculations but change the Run-time target to 50?

Best regards,
Reece

Put 50 in H10 to see the effect - is that what you mean?

As for the formula, this outer part:

=IF(D10="","", ... )

ensures that D10 contains something - otherwise it returns a blank (a simple way of avoiding many errors).

The next part:

... IF(COUNTIF(D$10:D10,D10)=1, MIN( ..1.. ), MIN( ..2.. ))

looks to see if this is the first instance of the Part Number in column D - if it is it will calculate the first MIN and if not it will calculate the second MIN.

MIN(..1..) is given by:

... MIN(H10,IFERROR(VLOOKUP(D10,'SMT Timings'!A:F,6,FALSE),"")) ...

and the second parameter is the same as you originally had, i.e. the default hourly number. The first parameter is H10, i.e. the required number, so the MIN function will take the lower of these two values. This caters for the situation where you might only want 100 PCBs, but the default value is above this.

MIN(..2..) is given by:

... MIN(VLOOKUP(D10,$D$10:$H10,5,0)-SUMIF(D9:D$10,D10,O9:O$10),VLOOKUP(D10,'SMT Timings'!A:F,6,FALSE)) ...

and again the second parameter here is the same as you had before (the IFERROR around it is not really needed, as this expression will only be evaluated for the second or subsequent occurrences of the part number, so it will definitely exist). The first parameter takes the required number of items from column H, and subtracts from it the number of items already recorded on previous rows for that same item. This is obtained by anchoring the row at 10 for the criteria range and sum range. This expression ensures that if the number required can only be supplied in 3 or more rows, then on the second row it will return the default value, as that will be less than the required value minus one default.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete