Date+7 formula in Table Header not working

[Melimob]

Hi

I have a table where the headers are weeks.
I want the user to update the first column header and the subsequent headers would update by each week.

e.g. Column A is 10-Jun and formula in column b onwards is A1+7 etc

10-Jun

17-Jun

24-Jun

01-Jul

08-Jul

15-Jul

<tbody>
</tbody>

If I drag the formula in a table it doesn't work unless it's in a table?

any way round this?

Many thanks

 

[steve the fish]

It will happily work if it isnt a table. You put the date in A1. Put the formula in B1 and then drag just B1, not A1 and B1, to the right.

 

[steve the fish]

Sorry i have misread. You cant for table headers.

 

[steve the fish]

You could use a macro if you wanted? Change the sheet and table name to suit.

With Sheets("Sheet1")
    d = CDate(.ListObjects("Table1").HeaderRowRange(1))
    For Each c In .ListObjects("Table1").HeaderRowRange.Cells
        c.Value = d
        d = d + 7
    Next
End With