Forum Discussion

RTick635's avatar
RTick635
Copper Contributor
Sep 19, 2023

Copy value per week

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 val...
Macros and VBA
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Sep 19, 2023

RTick635 

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:

  1. Create a Date Column:
    • In Sheet2, create a column to enter the dates corresponding to each week. You can start with the current date and then add 7 days to it for each subsequent week.
  2. Determine the Target Column:
    • In Sheet2, create a formula to determine the target column based on the date. You can use a formula like this in the cell where you want the value to appear (e.g., starting in cell B2):

=CHAR(64+DATEDIF($A2,TODAY(),"d")/7+2)

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.).

  1. Copy Values Based on the Target Column:
    • In the cell where you want to display the value for each week (e.g., cell B2), use a formula like this:

=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.

  1. Drag the Formulas:
    • Drag the formula from Step 3 to fill down for as many rows as needed to cover your data.

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.

RTick635's avatar
RTick635
Copper Contributor
Sep 20, 2023

NikolinoDE 

Thx for your replay

But :smile:

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

 

Resources