Automatic udates on a summary sheet
 

Hello,

I am trying to create a weekly report that has automaticly updated
summary sheet. I have created a a data sheet with all 52 weeks, each
week looks like below. All of each week ending data ranges are named
with the week ending date. For example the range below is named
01/03/06.

Shipping Wean Rcv. PSR
Strain Male Female Male Female Male Female
A 60 710 347 690 17% 13%

B 103 1429 574 1326 18% 18%

C 10 200 192 416 5% 48%

D 0 210 111 162 0% 30%

E 0 168 39 93 0% 81%

F 0 10 53 85 0% 12%

G 0 0 40 43 0% 0%

H 0 0 17 36 0% 0%

I am trying to find a way to reference the whole data range for a given
week on the summary sheet. Basically I want to be able to type in the
the week ending date in a cell on the summary and have that data range
show up on the summary sheet in the range A5:G21.

Thanks

Automatic udates on a summary sheet
 

Assuming the 52 weekly ranges are identically sized* and named as say: Wk1,
Wk2, ... *assumed to be: 17Rx7C since you mentioned A5:G21 in your post

In the summary sheet (this can work in any sheet),
assume A1 will house the input for the defined name, eg: Wk1
and we want the 17Rx7C to populate into A5:G21

Put in A5:
=IF($A$1="","",INDEX(INDIRECT($A$1),ROW(A1),COLUMN (A1)))
Copy A5 across to G5, fill down to G21

Empty source cells, if any, will be returned as zeros within A5:G21 but we
can suppress extraneous zeros from showing in the sheet via clicking: Tools
Options View tab Uncheck "Zero values" OK

The above should deliver the required results ..

Ps: wrt your line:
.. For example the range below is named 01/03/06.
I don't know how you managed to get the name: 01/03/06
accepted by Excel <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Hello,

I am trying to create a weekly report that has automaticly updated
summary sheet. I have created a a data sheet with all 52 weeks, each
week looks like below. All of each week ending data ranges are named
with the week ending date. For example the range below is named
01/03/06.

Shipping Wean Rcv. PSR
Strain Male Female Male Female Male Female
A 60 710 347 690 17% 13%

B 103 1429 574 1326 18% 18%

C 10 200 192 416 5% 48%

D 0 210 111 162 0% 30%

E 0 168 39 93 0% 81%

F 0 10 53 85 0% 12%

G 0 0 40 43 0% 0%

H 0 0 17 36 0% 0%

I am trying to find a way to reference the whole data range for a given
week on the summary sheet. Basically I want to be able to type in the
the week ending date in a cell on the summary and have that data range
show up on the summary sheet in the range A5:G21.

Thanks

Automatic udates on a summary sheet
 

Wow! Thank You it works!

As for the naming I ment it is named "Jan3". Sorry I was a bit spaced
when I typed that one last night.


Max wrote:
Assuming the 52 weekly ranges are identically sized* and named as say: Wk1,
Wk2, ... *assumed to be: 17Rx7C since you mentioned A5:G21 in your post

In the summary sheet (this can work in any sheet),
assume A1 will house the input for the defined name, eg: Wk1
and we want the 17Rx7C to populate into A5:G21

Put in A5:
=IF($A$1="","",INDEX(INDIRECT($A$1),ROW(A1),COLUMN (A1)))
Copy A5 across to G5, fill down to G21

Empty source cells, if any, will be returned as zeros within A5:G21 but we
can suppress extraneous zeros from showing in the sheet via clicking: Tools
Options View tab Uncheck "Zero values" OK

The above should deliver the required results ..

Ps: wrt your line:
.. For example the range below is named 01/03/06.
I don't know how you managed to get the name: 01/03/06
accepted by Excel <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Hello,

I am trying to create a weekly report that has automaticly updated
summary sheet. I have created a a data sheet with all 52 weeks, each
week looks like below. All of each week ending data ranges are named
with the week ending date. For example the range below is named
01/03/06.

Shipping Wean Rcv. PSR
Strain Male Female Male Female Male Female
A 60 710 347 690 17% 13%

B 103 1429 574 1326 18% 18%

C 10 200 192 416 5% 48%

D 0 210 111 162 0% 30%

E 0 168 39 93 0% 81%

F 0 10 53 85 0% 12%

G 0 0 40 43 0% 0%

H 0 0 17 36 0% 0%

I am trying to find a way to reference the whole data range for a given
week on the summary sheet. Basically I want to be able to type in the
the week ending date in a cell on the summary and have that data range
show up on the summary sheet in the range A5:G21.

Thanks

Automatic udates on a summary sheet
 

You're welcome !
Thanks for calling back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Wow! Thank You it works!

As for the naming I ment it is named "Jan3". Sorry I was a bit spaced
when I typed that one last night.