Use a cell as a sheet reference

  • Thread starter Thread starter Tony Hewitt
  • Start date Start date
T

Tony Hewitt

Hi guys and gals,

I've managed to come across a young lady, who needs to add values from
several pages in a workbook.

Each worksheet in the workbook is named according to the date it's relevant
to, i.e. 141103 - 14/11/03 (UK format).

They want to use this workbook (containing roughly 356 sheets (1 per day))
to produce weekly and monthly reports.

Is it possible to use a value from a cell as a sheet reference??

HELP!?!?!?!?!?

Many thanks

Tony Hewitt
 
One way, in a summary sheet put the first date in let's say A2 and copy down

in B2 put

=TEXT(DATE(YEAR(A2),MONTH(A2),DAY(A2)),"ddmmyy")

copy down to B366 or something

In C2 put

=IF(MOD(ROW(1:1),7)=1,"New Week","")

copy down

In D2

=MONTH(A2)

copy down

now you can use to sum A1:A10 for one week

=SUM(INDIRECT("'"&B2:B8&"'!A1:A10"))

for a month

=SUM(INDIRECT("'"&B2:B31&"'!A1:A10"))

and so on
 
Back
Top