How do I specify a formula to look in another cell for a sheet name?

[Elena Minguillon]

I am trying to get a formula to look in another cell for the name of a
different sheet (withing the same workbook) it needs to reference.

i.e. I want to count applicants for different jobs. Sheet 1 holds
the totals across the company whilst sheets 2 and 3 carry the
applicant data for Southern and Midland regions respectively.

Sheet 1
Reference Area No of Applicants
AAA01 Southern ??????
AAA02 Midlands
AAA03 Southern

Sheet 2 - Southern
Reference Name
AAA01 Jones
AAA01 Smith
AAA03 White
AAA01 Black

I need to get the 3rd column to look at the sheet specified in Column
2 (Southern) and count the number of times the reference in Column 1
(AAA01) appears in Column 1 of the relevant sheet. I don't want to
have to write the name of the sheet in the formula of Column 3 every
time I want to count the applicants for a new position.

Is there a simple way to do this?

Elena

 

[Frank Kabel]

Hi
have a look at INDIRECT in combination with COUNTIF. e.g.
=COUNTIF(INDIRECT("'" & B1 & "'!A:A"),A1)