use contents of a cell as a cell reference in a formula?

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,388
I want to use:

=COUNTIF(January!B7:B37,summary!A12)

except I want to have the sheet name 'January' taken from a cell so I can perform the countif on a large number of sheets

Is there a syntax which allows January!B7:B37 to be replaced with "A1"!B7:B37 in a formula where A1 contains January (or another month etc)?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

You could test following formula :

Code:
=COUNTIF(INDIRECT(A1&"!B7:B37"),Summary!A12)

Hope this will help
 

Watch MrExcel Video

Forum statistics

Threads
1,108,787
Messages
5,524,870
Members
409,607
Latest member
charleswaynemc

This Week's Hot Topics

Top