Inserting contents of cell as text into formula using cell referen

I have a spreadsheet for my staff where they log their time against every job they do. Each member of staff has their own work sheet where they enter this data. I am trying to create a summary sheet where I can enter the name of the member of staff (which equates to the worksheet name) to show me how much time they are spending on each job I have on my books. I want to be able to type the name of a person (or worksheet name) into a cell and the various functions in my summary sheet use that text string in that cell as the worksheet reference in the formulas I have entered.

for example one formula I have is:

=SUMIF(JoeBloggs!$F$17:$F$66,JoeBloggs!$A10,JoeBlo ggs!$L$17:$L$66)

But I want to put "JoeBloggs" or any other name in say cell A1 and have the formula update the worksheet reference to reflect the contents of A1.

Am I on the right tracks or is there a simpler way to obtain the same results.

Cheers

AdCook

Try

=SUMIF(INDIRECT(A1&"!$F$17:$F$66"),INDIRECT(A1&"!$ A10"),INDIRECT(A1&"!$L$17:
$L$66"))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"AdCook" wrote in message
...
I have a spreadsheet for my staff where they log their time against every
job they do. Each member of staff has their own work sheet where they enter
this data. I am trying to create a summary sheet where I can enter the name
of the member of staff (which equates to the worksheet name) to show me how
much time they are spending on each job I have on my books. I want to be
able to type the name of a person (or worksheet name) into a cell and the
various functions in my summary sheet use that text string in that cell as
the worksheet reference in the formulas I have entered.

for example one formula I have is:

=SUMIF(JoeBloggs!$F$17:$F$66,JoeBloggs!$A10,JoeBlo ggs!$L$17:$L$66)

But I want to put "JoeBloggs" or any other name in say cell A1 and have
the formula update the worksheet reference to reflect the contents of A1.

Am I on the right tracks or is there a simpler way to obtain the same
results.

Cheers

AdCook

Thanks Bob that did the job!

"Bob Phillips" wrote:

Try

=SUMIF(INDIRECT(A1&"!$F$17:$F$66"),INDIRECT(A1&"!$ A10"),INDIRECT(A1&"!$L$17:
$L$66"))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"AdCook" wrote in message
...
I have a spreadsheet for my staff where they log their time against every
job they do. Each member of staff has their own work sheet where they enter
this data. I am trying to create a summary sheet where I can enter the name
of the member of staff (which equates to the worksheet name) to show me how
much time they are spending on each job I have on my books. I want to be
able to type the name of a person (or worksheet name) into a cell and the
various functions in my summary sheet use that text string in that cell as
the worksheet reference in the formulas I have entered.

for example one formula I have is:

=SUMIF(JoeBloggs!$F$17:$F$66,JoeBloggs!$A10,JoeBlo ggs!$L$17:$L$66)

But I want to put "JoeBloggs" or any other name in say cell A1 and have
the formula update the worksheet reference to reflect the contents of A1.

Am I on the right tracks or is there a simpler way to obtain the same
results.

Cheers

AdCook

Thanks Bob that did the job!

"Bob Phillips" wrote:

Try

=SUMIF(INDIRECT(A1&"!$F$17:$F$66"),INDIRECT(A1&"!$ A10"),INDIRECT(A1&"!$L$17:
$L$66"))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"AdCook" wrote in message
...
I have a spreadsheet for my staff where they log their time against every
job they do. Each member of staff has their own work sheet where they enter
this data. I am trying to create a summary sheet where I can enter the name
of the member of staff (which equates to the worksheet name) to show me how
much time they are spending on each job I have on my books. I want to be
able to type the name of a person (or worksheet name) into a cell and the
various functions in my summary sheet use that text string in that cell as
the worksheet reference in the formulas I have entered.

for example one formula I have is:

=SUMIF(JoeBloggs!$F$17:$F$66,JoeBloggs!$A10,JoeBlo ggs!$L$17:$L$66)

But I want to put "JoeBloggs" or any other name in say cell A1 and have
the formula update the worksheet reference to reflect the contents of A1.

Am I on the right tracks or is there a simpler way to obtain the same
results.

Cheers

AdCook