Sep 19 2023 07:57 AM
Sep 19 2023 07:57 AM
I have a stock list which i also use to order stock.
Now i want to also create a analyse file that keep track of what i order per week so that i can make in the future a forecast.
Now i let the value that i put in the order column to my analys working sheet but what i want is that automatically after a week it shifts to the next column
So for instance this week it copy the value to column B and the next week to column C and so on
I hope my explanation is clear
Sep 19 2023 02:22 PM
To automatically shift the values from the "Order" column to a new column each week in your analysis sheet, you can use Excel formulas and a date-based approach. Here is how you can set this up:
Assuming you have your "Order" column in Sheet1 and you want to copy these values to columns in Sheet2 for each week, you can follow these steps:
This formula calculates the difference in days between the date in column A and today's date, then divides it by 7 to get the number of weeks. It adds 2 and converts the result to the corresponding column letter (e.g., B for week 1, C for week 2, etc.).
=IF(Sheet1!$A2="", "", IF(Sheet2!$A2="", Sheet1!$B2, Sheet2!$B2))
This formula checks if there is a value in the "Order" column of Sheet1 for the corresponding row. If there is a value, it copies it to the current week's column in Sheet2. If the current week's column in Sheet2 already has a value (from a previous week), it does not overwrite it.
Now, when you enter an order in the "Order" column in Sheet1, it will automatically copy to the appropriate week's column in Sheet2 based on the date. Each week, the order values will shift to the next column. You can adjust the date calculation or starting date as needed to align with your specific weeks. Another option would be using Excel filters. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
Sep 20 2023 05:42 AM
Thx for your replay
I have in my sheet 1 a hidden column that adds the order amounts for a week together for 1 product
And at Sunday it sets it to zero again, it also copy it to sheet 2, only thing that i now need is that every week it use a new column in my sheet 2
(added a simplified example)
(Code i use to add and clear the hidden column)
Sometimes a example say more then a 1000 words
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("I5:I58")) Is Nothing And Target.Count = 1 Then Target.Offset(, 3) = Target.Offset(, 3) + Target End Sub Private Sub Workbook_Open() If Weekday(Date) = vbSunday Then With Worksheets("Sheet1") .Range("L5:L58").ClearContents End With End If End Sub