Say I have week ones figures in cells in row 1 columns A1 to A5 and week two
figures in columns A6 to A10.
I have a summary page that links to cells A1 to A5 to show them there. Would
it be possible then in week two for the summary page to automatically update
the reference cells to show cells A6 to A10 instead of A1 to A5?
Basically is there any time based function or macro that could do this?

Hi,

Try something like this:

=SUM(INDIRECT("A"&ROW(A1)*5-4&":A"&ROW(A1)*5))

copied down, if you need to copy across then change ROW for COLUMN.

HTH
Jean-Guy

"Blade370" wrote:

Say I have week ones figures in cells in row 1 columns A1 to A5 and week two
figures in columns A6 to A10.
I have a summary page that links to cells A1 to A5 to show them there. Would
it be possible then in week two for the summary page to automatically update
the reference cells to show cells A6 to A10 instead of A1 to A5?
Basically is there any time based function or macro that could do this?

So this would automatically change the values of the cells on my summary page
to show the next weeks figures instead of the previous weeks?

"pinmaster" wrote:

Hi,

Try something like this:

=SUM(INDIRECT("A"&ROW(A1)*5-4&":A"&ROW(A1)*5))

copied down, if you need to copy across then change ROW for COLUMN.

HTH
Jean-Guy

"Blade370" wrote:

Say I have week ones figures in cells in row 1 columns A1 to A5 and week two
figures in columns A6 to A10.
I have a summary page that links to cells A1 to A5 to show them there. Would
it be possible then in week two for the summary page to automatically update
the reference cells to show cells A6 to A10 instead of A1 to A5?
Basically is there any time based function or macro that could do this?

Hi,

If you mean you only want to use 1 cell for the totals then no, the formula
needs to be drag down or across to show different week totals on different
rows or columns. To use only 1 cell then maybe something like this:

=SUM(INDIRECT("A"&WEEKNUM(TODAY())*5-4&":A"&WEEKNUM(TODAY())*5))

or you can use another cell for the week number..... say A1

=SUM(INDIRECT("A"&A1*5-4&":A"&A1*5))
to change week number simply type it in A1 or use the formula:
=WEEKNUM(TODAY())

Note: don't forget to include the referenced sheet name in the formula.

=SUM(INDIRECT("Sheet1!A"&A1*5-4&":A"&A1*5))

HTH
Jean-Guy

"Blade370" wrote:

So this would automatically change the values of the cells on my summary page
to show the next weeks figures instead of the previous weeks?

"pinmaster" wrote:

Hi,

Try something like this:

=SUM(INDIRECT("A"&ROW(A1)*5-4&":A"&ROW(A1)*5))

copied down, if you need to copy across then change ROW for COLUMN.

HTH
Jean-Guy

"Blade370" wrote:

Say I have week ones figures in cells in row 1 columns A1 to A5 and week two
figures in columns A6 to A10.
I have a summary page that links to cells A1 to A5 to show them there. Would
it be possible then in week two for the summary page to automatically update
the reference cells to show cells A6 to A10 instead of A1 to A5?
Basically is there any time based function or macro that could do this?

Hi
Please forgive my ignorance here but I don't think I explained myself
properly. I have two sheets as mentioned previously. On sheet one I have
figures in cells A1 to A5 for week one and A6 to A10 for week 2 etc.

Sheet two is a summary sheet and needs to show the figures for week in sheet
one in say cells c1 to c5. Then on week two these same cells will need to
show the figures from sheet 1 cells A6 to A10 automatically without my having
to change it manually. I know I mentioned sums and totals earlier but can you
just forget that, my mistake.

"pinmaster" wrote:

Hi,

If you mean you only want to use 1 cell for the totals then no, the formula
needs to be drag down or across to show different week totals on different
rows or columns. To use only 1 cell then maybe something like this:

=SUM(INDIRECT("A"&WEEKNUM(TODAY())*5-4&":A"&WEEKNUM(TODAY())*5))

or you can use another cell for the week number..... say A1

=SUM(INDIRECT("A"&A1*5-4&":A"&A1*5))
to change week number simply type it in A1 or use the formula:
=WEEKNUM(TODAY())

Note: don't forget to include the referenced sheet name in the formula.

=SUM(INDIRECT("Sheet1!A"&A1*5-4&":A"&A1*5))

HTH
Jean-Guy

"Blade370" wrote:

So this would automatically change the values of the cells on my summary page
to show the next weeks figures instead of the previous weeks?

"pinmaster" wrote:

Hi,

Try something like this:

=SUM(INDIRECT("A"&ROW(A1)*5-4&":A"&ROW(A1)*5))

copied down, if you need to copy across then change ROW for COLUMN.

HTH
Jean-Guy

"Blade370" wrote:

Say I have week ones figures in cells in row 1 columns A1 to A5 and week two
figures in columns A6 to A10.
I have a summary page that links to cells A1 to A5 to show them there. Would
it be possible then in week two for the summary page to automatically update
the reference cells to show cells A6 to A10 instead of A1 to A5?
Basically is there any time based function or macro that could do this?

Hi
Please excuse my ignorance here but I am fairly new to excel. I don't think
I explained myself correctly.

I have two spreadsheets were sheet one has figures and sheet two is a
summary sheet. On sheet one in cells A1 to A5 there are weeks ones figures.
In cells A6 to A10 there are weeks two figures and so on.
On sheet two I would like to be able to show the figures for cells A1 to A5
sheet 1 in cells C1to C5 on sheet 2. However on the second week I would like
these same cells to now show the figures that are in sheet one A6 to A10
automatically without me having to manually change it and so on for weeks
3,4,5,6 etc.
I know I previously mentioned sums and totals but can we please forget that.

cheers.

"pinmaster" wrote:

Hi,

If you mean you only want to use 1 cell for the totals then no, the formula
needs to be drag down or across to show different week totals on different
rows or columns. To use only 1 cell then maybe something like this:

=SUM(INDIRECT("A"&WEEKNUM(TODAY())*5-4&":A"&WEEKNUM(TODAY())*5))

or you can use another cell for the week number..... say A1

=SUM(INDIRECT("A"&A1*5-4&":A"&A1*5))
to change week number simply type it in A1 or use the formula:
=WEEKNUM(TODAY())

Note: don't forget to include the referenced sheet name in the formula.

=SUM(INDIRECT("Sheet1!A"&A1*5-4&":A"&A1*5))

HTH
Jean-Guy

"Blade370" wrote:

So this would automatically change the values of the cells on my summary page
to show the next weeks figures instead of the previous weeks?

"pinmaster" wrote:

Hi,

Try something like this:

=SUM(INDIRECT("A"&ROW(A1)*5-4&":A"&ROW(A1)*5))

copied down, if you need to copy across then change ROW for COLUMN.

HTH
Jean-Guy

"Blade370" wrote:

Say I have week ones figures in cells in row 1 columns A1 to A5 and week two
figures in columns A6 to A10.
I have a summary page that links to cells A1 to A5 to show them there. Would
it be possible then in week two for the summary page to automatically update
the reference cells to show cells A6 to A10 instead of A1 to A5?
Basically is there any time based function or macro that could do this?

Hi,

Ok got it. Try this:

=INDIRECT("Sheet1!A"&WEEKNUM(TODAY())*ROW($A$5)-(5-ROW(A1)))
copy down 5 rows.

It should show the values in A46:A50

HTH
Jean-Guy

"Blade370" wrote:

Hi
Please excuse my ignorance here but I am fairly new to excel. I don't think
I explained myself correctly.

I have two spreadsheets were sheet one has figures and sheet two is a
summary sheet. On sheet one in cells A1 to A5 there are weeks ones figures.
In cells A6 to A10 there are weeks two figures and so on.
On sheet two I would like to be able to show the figures for cells A1 to A5
sheet 1 in cells C1to C5 on sheet 2. However on the second week I would like
these same cells to now show the figures that are in sheet one A6 to A10
automatically without me having to manually change it and so on for weeks
3,4,5,6 etc.
I know I previously mentioned sums and totals but can we please forget that.

cheers.

"pinmaster" wrote:

Hi,

If you mean you only want to use 1 cell for the totals then no, the formula
needs to be drag down or across to show different week totals on different
rows or columns. To use only 1 cell then maybe something like this:

=SUM(INDIRECT("A"&WEEKNUM(TODAY())*5-4&":A"&WEEKNUM(TODAY())*5))

or you can use another cell for the week number..... say A1

=SUM(INDIRECT("A"&A1*5-4&":A"&A1*5))
to change week number simply type it in A1 or use the formula:
=WEEKNUM(TODAY())

Note: don't forget to include the referenced sheet name in the formula.

=SUM(INDIRECT("Sheet1!A"&A1*5-4&":A"&A1*5))

HTH
Jean-Guy

"Blade370" wrote:

So this would automatically change the values of the cells on my summary page
to show the next weeks figures instead of the previous weeks?

"pinmaster" wrote:

Hi,

Try something like this:

=SUM(INDIRECT("A"&ROW(A1)*5-4&":A"&ROW(A1)*5))

copied down, if you need to copy across then change ROW for COLUMN.

HTH
Jean-Guy

"Blade370" wrote:

Say I have week ones figures in cells in row 1 columns A1 to A5 and week two
figures in columns A6 to A10.
I have a summary page that links to cells A1 to A5 to show them there. Would
it be possible then in week two for the summary page to automatically update
the reference cells to show cells A6 to A10 instead of A1 to A5?
Basically is there any time based function or macro that could do this?