Referencing a sheet name

[zeeplocked]

Hello,
I'm just thinking if there's a way to ïnsert a Sheet name into a formula by refering to a cell?
Example:
A
1 Apple
2 Ball

I have two sheets: Apple and Ball
My Formula: =(COUNTIFS('Apple'!$A$1:$A$10,"Red",'Apple'!$b$1:$b$10,"Big",'Apple'!$C$1:$C$10,"Sweet")
Is there a simple way wherein I can just refer cell A1 for the sheet name instead of typing "Äpple""three times?

 

[RoryA]

You can use INDIRECT and pass it the range address as text (including the sheet name - so:

'Apple'!$A$1:$A$10

becomes:

INDIRECT("'"&A1& "'!$A$1:$A$10")

 

[igold]

Which method involves the smallest number of keystrokes, which I assume the OP is looking to do...

 

[zeeplocked]

Thanks for your replies but it is Error

 

[RoryA]

The whole formula would be:

=COUNTIFS(INDIRECT("'"&A1&"'!$A$1:$A$10"),"Red",INDIRECT("'"&A1&"'!$B$1:$B$10"),"Big",INDIRECT("'"&A1&"'!$C$1:$C$10"),"Sweet")