Formula to store values for each week maintaining the values of previous weeks?

[Fractalis]

Hi all,

I have a table like below that has in first column some values for current week. Then I want to make a track of each week and then I need to copy manually with "copy and paste values" the values of current week2, then do the same for week3 and so on and show empty the future weeks. This is a small version of actual table.

In row 5 of each week column I do a subtraction of "first value of week-N - first value of week-N-1". For example in B5=B2-A2, in C5 = C2-B2.

Well, what I'm trying to do is a way to copy the values of column A in column of corresponding week. If this week is week 4, then copy values of column A in column D. The problem
is that a simple formula like D2=A2 is that in next week D2 will have the values of "current week" in the future and all columns will have the same values.

My current attempt with this formula

=IF(ISOWEEKNUM(TODAY()) =4,$A2,"")

is to evaluate which is the current week. If is the 4th week, then would be equal to column A values, else empty. the problem with my formula is that will show empty the values for previous weeks.

I hope this makes sense and could be done with a formula or only with VBA?' Thanks for any help.

Book1
	A	B	C	D	E
1	Current Week	Week1	Week3	Week4	Week5
2	75	47	56	75
3	12	32	85	12
4	43	63	14	43
5	96	-28	9	19
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=IF(ISOWEEKNUM(TODAY()) =4,$A2,"")
E2:E4	E2	=IF(ISOWEEKNUM(TODAY()) =5,$A2,"")
B5:C5	B5	=B2-A2
D5	D5	=IF(ISOWEEKNUM(TODAY()) =4,D2-C2,"")

 

[Peter_SSs]

There are some things about your sheet that are a bit puzzling to me

• There is no Week2 column
• Why is there a value in B5? It seems to me that for Week1 there would not be a value from the previous week but if there was, it would not be the value in A2.
• Why is there 4 values in column A when the other columns have 3 and then a formula for the previous week difference?

What you are trying to do really needs to use vba to achieve. Assuming that is okay with you and the worksheet start like this ..

Fractalis.xlsm
	A	B	C	D	E
1	Current Week	Week1	Week2	Week3	Week4
2	75	47	56
3	12	32	85
4	43	63	14
5			9
Sheet1

Cell Formulas
Range	Formula
C5	C5	=C2-B2

.. you could try this worksheet 'event' code with a copy of your workbook. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by double-clicking cell A1 once the new figures have been entered into column A as shown above.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim NextCol As Long
  
  If Target.Address(0, 0) = "A1" Then
    Cancel = True
    NextCol = Cells(2, Columns.Count).End(xlToLeft).Column + 1
    With Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cells(2, NextCol).Resize(.Count).Value = .Value
      .ClearContents
      If NextCol > 2 Then .Cells(.Count + 1, NextCol).FormulaR1C1 = "=R2C-R2C[-1]"
    End With
  End If
End Sub

Starting with the sheet shown above, this is the result for me after double-clicking A1. The values have been transferred to next vacant column, the formula has been added at the bottom and column A has been cleared out ready for the following week's numbers.

Fractalis.xlsm
	A	B	C	D	E	F
1	Current Week	Week1	Week2	Week3	Week4
2		47	56	75
3		32	85	12
4		63	14	43
5			9	19
6
Sheet1

Cell Formulas
Range	Formula
C5	C5	=C2-B2
D5	D5	=D$2-C$2

 

[Fractalis]

There are some things about your sheet that are a bit puzzling to me

• There is no Week2 column
• Why is there a value in B5? It seems to me that for Week1 there would not be a value from the previous week but if there was, it would not be the value in A2.
• Why is there 4 values in column A when the other columns have 3 and then a formula for the previous week difference?

What you are trying to do really needs to use vba to achieve. Assuming that is okay with you and the worksheet start like this ..

Hi @Peter_SSs Thanks so much for your help. Regarding Week2 it was a typo. The actual table has about 50 rows and 12 subtraction formulas like that between initial and final row.

Your macro it works very nice, but since I'm using the workbook online I see that only typescript macro works online. Is there a way to translate your code to work online? or should I open
another thread? Thanks in advance.

 

[Peter_SSs]

Is there a way to translate your code to work online?

For me that is not an area of strength.

or should I open another thread?

I suggest that you open another thread and explain the use of online Excel for the problem. I will close this thread so we don't have two on the same topic.
In the new thread you can put a link to this one if you think it will assist any helpers.