Ordering against a forecast according to lead-time

[K. Georgiadis]

I cannot yet visualize whether this absolutely requires VBA code so please bear with me:

I will have a worksheet with years running across Row 4; Row 5 will have quarters 1 to 4, as subdivisions of each year. In Row 6 the user will enter a sales forecast by quarter, as many as 5 years at a time In cell B2 the user will enter the lead time for obtaining the product, say 12 weeks. And now for my challenge: Row 7 will have a calculated "ordering schedule", indicating when the product must be ordered to be available for sale, based on the lead time entered. I shall make the assumption that product required in Qrt 3 must be in the warehouse at the beginning of the quarter, therefore if the lead time is only a fraction of a quarter, it still has to be ordered in the previous quarter.

I have attached a simple worksheet illustrating the contemplated design (devoid of any calculations of logical formulas!)

My question is:

Does this require VBA code or can you think of a way to solve the problem with built-in Excel functions?

Thanks for your help!

[Paleo]

I think you must use VBA because according to the leading time it might require you to order 2 or more quarters in advance and its hard to code using only regular excel formulas.

[Paleo]

Hi,

use this macro at Sheet1 Module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Lead As Integer, Retorno As Integer, Coluna As Variant
    Lead = Range("B2")
    Coluna = Array("B", "C", "D", "E", "F", "G", "H", "I")
    Retorno = Range("D2")
    For Each col In Coluna
        Range(col & 6).Activate
        If Range(col & 6) > ActiveCell.Offset(1, -Retorno) Then
            z = MsgBox("No products available due to lead time for " & Range(col & 5), vbOKOnly)
        End If
    Next
End Sub

[K. Georgiadis]

I thought so. Can you (or someone) give me some suggestions?

[Paleo]

Doesnt my code from the last post helped you out?

[K. Georgiadis]

I'm sorry, I didn't see the code. I'll try it now. Thanks!

[Paleo]

Ok, no problem if I didnt address your problem correctly just let me know and I will try to adapt it as needed.

[K. Georgiadis]

I tried it, but it doesn't seem to be the answer. I'm hoping for a macro that will auto-populate row 7 according to the lead-time entered in cell B2. I have also thought of giving the user the option of using this auto-calculation or entering the numbers manually in row 7. Perhaps a checkbox "Do you wish to auto calculate the ordering schedule?". Checking the box activates the auto-populate macro.

[Paleo]

Ok, I will made the changes and submit the file then.

[Paleo]

Try this one.

[K. Georgiadis]

Thanks so much for the effort, Paleo. I tried your file and ran into an error condition after cycling through the program 3 times (by entering different numbers in the lead time cell). In the meantime, though, I put my traditional Excel thinking cap on and was able to resolve this problem by using multiple IF and AND statements, as shown in the attached modified file. It seems to work well, even though an unexpected flaw can still arise!

What I need to do next with this file is to:

1. Add Row 7 labeled "Manual entry of ordering schedule"
2. add a checkbox asking if the user wants to use auto-calculation for the ordering schedule.
2. If the checkbox is checked, Row 6 is unhidden, Row 7 is hidden
3. If the checkbox is NOT checked, the reverse occurs: Row 6 is hidden, Row 7 is unhidden

I believe I have sample code somewhere for a checkbox functioning in that fashion, but any suggestions you may have are most welcome.

Also, do you think that it would be more functional if, instead of a checkbox, I provided two option buttons, one for auto, the other for manual operation, hiding or unhiding the appropriate rows?

Again, many thanks for your help.

[Paleo]

Hi, I think you might use both approaches, I will make it using the option for you first and then I post it here.

[K. Georgiadis]

Thanks!!

[Paleo]

Check if this solves your problem.

[K. Georgiadis]

perfect. I'm marking SOLVED. Thanks again

[K. Georgiadis]

Carlos, I have a follow-on question. The example I have provided you with covered just one product, whereas the actual worksheet will cover 10 products. Thus, the headings, rows, formulas, etc. will copied and pasted down 9 times. What would be the syntax if I wanted those two Option Buttons to hide/unhide not only rows 6 and 7, but all appropriate rows for the remaining 9 products, e.g. 19 and 20; 27 and 28 and so on...

[Paleo]

Hi you just need to insert that in the event, the better way is by using a loop. If you can post a worksheet with this specs (can of course be with no real data in it, just the rows and cols you will use) I can create it for you.

[K. Georgiadis]

Here it is (Version 5). I see this as a valuable lesson; I hope to be able to do this on my own next time

[Paleo]

Hi I have just downloaded it. Soon will be posting your solution.

[Paleo]

Hi, as you used always the same text in column A I have used a different approach.