Inserting contents of cell as text into formula using cell referen

[Guest]

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,JoeBloggs!$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

 

[Bob Phillips]

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)

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,JoeBloggs!$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.

 

[Guest]

Thanks Bob that did the job!

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)


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.
the formula update the worksheet reference to reflect the contents of A1.

 

[Guest]

Thanks Bob that did the job!

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)


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.
the formula update the worksheet reference to reflect the contents of A1.