Using cell values as sheet reference

[aesoprockin]

Hi guys. Here's what I'm trying to do.

Column A contains names.
A2=name1
A3=name2
A4=name3

Then I have sheet1 and then every name has its own sheet. So there is a sheet called name1, a sheet called name2, etc.

I'd like to sum C:C on name1, but I'd like to reference the sheet by referencing A2, this way I can drag the formula down.

So I don't want to =sum(name1!C:C)
I'd like to somehow =sum(a2!C:C)

Maybe by use of indirect or something idk.

 

[etaf]

=SUM(INDIRECT("'"&A2&"'!C:C"))
but the range will not change when copied - as its in "" so will always be C:C

 

[aesoprockin]

=SUM(INDIRECT("'"&A2&"'!C:C"))
but the range will not change when copied - as its in "" so will always be C:C

Thanks! I actually figured it out after I posted.
It wasn't working because I had commas in the sheet names so I just made a column for last names and matched that for the sheet names.

 

[etaf]

you are welcome, thanks for the update