Any one able to assist with formulae like the above which for me is quite
advanced yet I am sure it is intermediate.

I would like to attach a file to give as n example of what I am trying to
do. (How do I do this on the newsgroup?)

Have already got this far from an Excel MVP help and need to expand what I
am doing with a roster.

Any takers??

Mike

A good resource for SUMPRODUCT()

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200784


"MikeR-Oz" wrote:

Any one able to assist with formulae like the above which for me is quite
advanced yet I am sure it is intermediate.

I would like to attach a file to give as n example of what I am trying to
do. (How do I do this on the newsgroup?)

Have already got this far from an Excel MVP help and need to expand what I
am doing with a roster.

Any takers??

Mike

Thanks for that.

I am trying to have the formulae refer to to seperate ranges of cells and
refer = to names on a worksheet within the workbook.

I do not think the reference you have provided will assit with this aspect -
but appreciate the support.

Mike

"Gary''s Student" wrote:

A good resource for SUMPRODUCT()

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200784


"MikeR-Oz" wrote:

Any one able to assist with formulae like the above which for me is quite
advanced yet I am sure it is intermediate.

I would like to attach a file to give as n example of what I am trying to
do. (How do I do this on the newsgroup?)

Have already got this far from an Excel MVP help and need to expand what I
am doing with a roster.

Any takers??

Mike

.. like to attach a file to give as an example ..

You can upload your sample* using a free filehost
and post a **link** to it here
*desensitized as appropriate

Eg, you could use this free filehost:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload your sample,
then paste the link into your reply here
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

n

Max - Thaks . The link is

http://www.freefilehosting.net/download/3gj79

To try and explain what I am after:

There is 2 rosters for 2 teams working 8 weeks.

Each Line or row attracts a set number of hours , so if a staff member is on
that particular line (row) for that week then he will have been assumed to
have worked the hours that the line(row) attracts.

I would like to automate the process so that a sheet containing the roster
indicates against the staff name the hours worked each week and the total
hours worked for the entire roster at the end. This to be for both Team A and
Team B roster.

The list of Staff names can be on a seperate sheet.

How can I get the 'Roster Hours' Sheet to auto poulate 'knowing' the hours
of each line by the staff members name??

Cheers
MIke
"Max" wrote:

.. like to attach a file to give as an example ..

You can upload your sample* using a free filehost
and post a **link** to it here
*desensitized as appropriate

Eg, you could use this free filehost:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload your sample,
then paste the link into your reply here
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

One possible approach, implemented in this sample:
http://www.freefilehosting.net/download/3gj8j
Roster_Automation_1.xls

In Roster hours (2),
With Team A's names listed in A10:A13, Team B's in A14:A17

For Team A
In B10:
=IF(ISNA(MATCH($A10,OFFSET('Team A Roster'!$B$4:$B$7,,MATCH(B$3,'Team A
Roster'!$C$3:$G$3,0)),0)),"",INDEX('Line Hours
accumulation'!$B$4:$B$7,MATCH($A10,OFFSET('Team A
Roster'!$B$4:$B$7,,MATCH(B$3,'Team A Roster'!$C$3:$G$3,0)),0)))
Copy B10 across/fill down to F13 to populate

Similarly for Team B
In B14:
=IF(ISNA(MATCH($A14,OFFSET('Team B Roster'!$B$5:$B$8,,MATCH(B$3,'Team B
Roster'!$C$4:$G$4,0)),0)),"",INDEX('Line Hours
accumulation'!$B$4:$B$7,MATCH($A14,OFFSET('Team B
Roster'!$B$5:$B$8,,MATCH(B$3,'Team B Roster'!$C$4:$G$4,0)),0)))
Copy B14 across/fill down to F17 to populate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MikeR-Oz" wrote:
Max - Thaks . The link is

http://www.freefilehosting.net/download/3gj79

To try and explain what I am after:

There is 2 rosters for 2 teams working 8 weeks.

Each Line or row attracts a set number of hours , so if a staff member is on
that particular line (row) for that week then he will have been assumed to
have worked the hours that the line(row) attracts.

I would like to automate the process so that a sheet containing the roster
indicates against the staff name the hours worked each week and the total
hours worked for the entire roster at the end. This to be for both Team A and
Team B roster.

The list of Staff names can be on a seperate sheet.

How can I get the 'Roster Hours' Sheet to auto poulate 'knowing' the hours
of each line by the staff members name??

Cheers
MIke

But your formula works as is. In B1 thru B8 put:

1
2
3
4
5
6
7
8

and in C1 thru E8 put:

data 0 0
0 data 0
0 0 data
0 0 0
0 0 0
0 0 0
0 0 0
0 0 data

and in G1 put:

data

Your posted formula returns 14 (the correct value)
--
Gary''s Student - gsnu200784


"MikeR-Oz" wrote:

Thanks for that.

I am trying to have the formulae refer to to seperate ranges of cells and
refer = to names on a worksheet within the workbook.

I do not think the reference you have provided will assit with this aspect -
but appreciate the support.

Mike

"Gary''s Student" wrote:

A good resource for SUMPRODUCT()

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200784


"MikeR-Oz" wrote:

Any one able to assist with formulae like the above which for me is quite
advanced yet I am sure it is intermediate.

I would like to attach a file to give as n example of what I am trying to
do. (How do I do this on the newsgroup?)

Have already got this far from an Excel MVP help and need to expand what I
am doing with a roster.

Any takers??

Mike