Drag a sum of 7 days across so each new cell is the next week

[tashworth90]

Hey,

I dont know the best way to describe this. Basically i have data that has spend per day across the whole of the year. Then in another worksheet i want that data by week (starting sunday). So if the first cell in data by week is "='Data By Day'!C4:I4", i want the next cell to be "='Data By Day'!J4:P4" then "='Data By Day'!Q4:W4". Instead when i drag it across it goes "='Data By Day'!C4:I4" then the next cell is "='Data By Day'!D4:J4".

Is there a way i can drag a large amount of data across by the desired amount of cells each time rather than moving across in a relative fashion

Thanks in advance

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Richard Buttrey]

Hi,

If you're wanting the sum of C4:I4, J4:P4..etc, then the following in column A copied across

Please Login or Register  to view this content.

However you may wish to rethink your data layout so that you could analyse it with a Pivot Table and generate your totals by week.

[tashworth90]

Thanks heaps for your help, That formula is perfect but is there a way to change it so i can start in a different different column, so im starting in column C rather than A due to row headings?

I use pivots for this all the time and it is much easier. We just have a new client whose data is updated each week as things that were booked become applied so i have to overlay the data each week.

[martindwilson]

you need a different way for the start point column() just returns the col ref the formula is in
will be ok if placed in col a as when filled across it gives 1 then 2 then 3 but starting in col b would give 2,3,4
there are various methods to do that
columns($a:a) would give 1,2,3,4 as it counts the cols between the 2 references
so would columns($c:c) or columns($z:z)
change the formula to
=SUM(OFFSET('data by day'!$C4,0,COLUMNS($A:A)*7-7,1,7)) you can put it in any cell

[tashworth90]

Thanks mate, that will be very helpful