Reference value on worksheet from name in cell

[JMac]

I need to see if there is a way to reference a value in a worksheet
that is dependent on the name of the worksheet that is in an active
cell on the first worksheet.

Summary Sheet is like this:

A B
1 Clinic Name
2 Total number of patients
3 Carthage ='a3'!e24
4 Gallatin ='a4'!e24
....

I have many worksheets for many clinics...any ideas for simplicity?
Any code to replace the reference in the b column (replace: a3, or a4
so that it would work?)

TIA,

Josh

 

[Pete_UK]

Try this in B3:

=INDIRECT("'"&A3&"'!E24")

and copy down as required.

Hope this helps.

Pete

 

[JMac]

Try this in B3:

=INDIRECT("'"&A3&"'!E24")

and copy down as required.

Hope this helps.

Pete






- Show quoted text -

ok, I tried that and the only problem is that the 'value' that I
mentioned would be in the other sheet is actually a reference to
another sheet with the data using vlookup for information from the
clinic. it then returns just a blank cell.
??
-Josh

 

[Pete_UK]

I don't understand - please try to describe what you have (and what
you want to achieve) more accurately. Refer to actual sheet names and
ranges used etc.

Pete

 

[JMac]

I don't understand - please try to describe what you have (and what
you want to achieve) more accurately. Refer to actual sheet names and
ranges used etc.

Pete





- Show quoted text -

Pete-

Thanks for all your help. It was my mistake. I reference the wrong
cell so it wasn't getting the right results. it was refrerence a blank
cell on the worksheet, so it actually it worked.
Thanks again!

-Josh

 

[Pete_UK]

You're welcome, Josh - glad to be of help.

Pete