Automatic Cell Referencing

[Guest]

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?

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

I have done this in the second sheet but all I am getting is 0 in the cell.

 

[Guest]

Hi,

Did you adjust the formula to reference the correct sheet? I used Sheet1 as
an example so yours is probably different. Also which week number is it
suppose to lookup? The forlula I gave you will lookup week 10, the current
week number. If you want to specify the week number youself then put the week
number in a cell and reference that cell in the formula:

=INDIRECT("Sheet1!A"&$A$1*5-(5-ROW(A1)))

slight adjustment to the first formula:

=INDIRECT("Sheet1!A"&WEEKNUM(TODAY())*5-(5-ROW(A1)))

HTH
Jean-Guy

 

[Guest]

could you explain to me what this part of the formula is doing?
*5-(5-ROW(A1)))
and mine is in columns so i guess i just change row to column?

 

[Guest]

Hi,

A simple way of seeing what it is doing is to type it as a formula then drag
it down.

=5-(5-ROW(A1))

when dragged down you will get

=5-(5-ROW(A1))
=5-(5-ROW(A2))
=5-(5-ROW(A3))
=5-(5-ROW(A4))
=5-(5-ROW(A5))

which is the same as:

=5-(5-1)
=5-(5-2)
=5-(5-3)
=5-(5-4)
=5-(5-5)

which equals

1
2
3
4
5

so if we take week 1 as an example, we get

Sheet1!A1
Sheet1!A2
Sheet1!A3
Sheet1!A4
Sheet1!A5

I'm not very good at explaing things but I hope I made it clearer for you.

HTH
Jean-Guy

 

[Guest]

Hi,
That is great and thanks for your patience with me. I think I know what I
am doing now.

 

[Guest]

Glad I could help, and thanks for the feedback!

Regards!
Jean-Guy