How do I refer to a sheet name in this formula?

[bigpat]

I have a formula: =SUMIF(DINOSAUR!$C$12:$C$37,K$3,DINOSAUR!$A$12:$A$37) where DINOSAUR is the name of another sheet in my workbook (it's also the name of a clinical trial)

But the name also appears in cell F4 as shown here. Now I need to replicate this formula down the sheet for many other studies. How can I refer to cell F4 in this formula so that it will refer to the next sheet each time? I have made sure that the sheet names exactly match the entries in column F.

I tried simply replacing DINOSAUR with F4, I have also tried quotes and square brackets, but I can't make it work.

Ideally the formula should allow for spaces in the sheet names, but if that's a problem I can RenameThemLikeThis.

[/URL][/IMG]

 

[Norie]

Use INDIRECT.

=SUMIF(INDIRECT("'"&F4&"'!$C$12:$C$37"), K$3, INDIRECT("'"&F4&"'!$A$12:$A$37"))

 

[bigpat]

Use INDIRECT.

=SUMIF(INDIRECT("'"&F4&"'!$C$12:$C$37"), K$3, INDIRECT("'"&F4&"'!$A$12:$A$37"))

Fantastic - thank you! I'd never heard of INDIRECT before and I need to read up little on it, especially to wrap my head round all those quotes. It seems a weird name for it! I needed to make it $F4 but I hadn't clarified that I need the formula to replicate across other columns too. But I figured that part out.

Thanks again. This is a big help.

 

[Norie]

All those quotes are only really there in case you have a sheet name with a space, you could remove them but it's probably worth keeping them just in case.