Variable Worksheet Name in Formula

Good morning --

I have a workbook storing similar data on multiple worksheets. I have a
'Report' page, from which I'd like to abstract the right person's data onto a
jpeg form and print it out. I'd like to be able to put the person's name in
a cell at the top of the page, and have formulas extract the data from the
associated worksheet.

So, if I have worksheets labeled 'Smith', 'Jones', and 'Wesson', on the
'Report' sheet I'd like to put 'Smith' in, say, cell B1 and have a formula
pick up that value and go to the 'Smith' page to pull in the info from, say,
cell C50.

Is that possible without VBA code?

TIA

GarysStudent
Sat Mar 15 11:43:00 CDT 2008

=INDIRECT(B1 & "!C50")
--
Gary''s Student - gsnu200773

pdberger
Sat Mar 15 11:47:00 CDT 2008

Gary's Student --

Grasshopper, you are a student no longer! Many thanks. Saved me DAYS of
work.

(If you're too young for the 'grasshopper' reference -- congrats, and I can
let you know where it's from...)

peter

"Gary''s Student" wrote:

> =INDIRECT(B1 & "!C50")
> --
> Gary''s Student - gsnu200773

T
Sat Mar 15 11:47:06 CDT 2008

Try this:

=IF(B1="","",INDIRECT("'"&B1&"'!C50"))


--
Biff
Microsoft Excel MVP


"pdberger" <pdberger@discussions.microsoft.com> wrote in message
news:5DF399A2-043C-4291-8526-AB827B405271@microsoft.com...
> Good morning --
>
> I have a workbook storing similar data on multiple worksheets. I have a
> 'Report' page, from which I'd like to abstract the right person's data
> onto a
> jpeg form and print it out. I'd like to be able to put the person's name
> in
> a cell at the top of the page, and have formulas extract the data from the
> associated worksheet.
>
> So, if I have worksheets labeled 'Smith', 'Jones', and 'Wesson', on the
> 'Report' sheet I'd like to put 'Smith' in, say, cell B1 and have a formula
> pick up that value and go to the 'Smith' page to pull in the info from,
> say,
> cell C50.
>
> Is that possible without VBA code?
>
> TIA

L
Sat Mar 15 19:27:57 CDT 2008

He's selling Yellow Book ads now...lol
Regards,
Howard

"pdberger" <pdberger@discussions.microsoft.com> wrote in message
news:5DF399A2-043C-4291-8526-AB827B405271@microsoft.com...
> Good morning --
>
> I have a workbook storing similar data on multiple worksheets. I have a
> 'Report' page, from which I'd like to abstract the right person's data
> onto a
> jpeg form and print it out. I'd like to be able to put the person's name
> in
> a cell at the top of the page, and have formulas extract the data from the
> associated worksheet.
>
> So, if I have worksheets labeled 'Smith', 'Jones', and 'Wesson', on the
> 'Report' sheet I'd like to put 'Smith' in, say, cell B1 and have a formula
> pick up that value and go to the 'Smith' page to pull in the info from,
> say,
> cell C50.
>
> Is that possible without VBA code?
>
> TIA