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

M

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,397
Office Version
  1. 2007
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)?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi,

You could test following formula :

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

Hope this will help
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Add to Data Validation Values From Data Entry Cell
Replies
4
Views
322
kvsrinivasamurthy
K
C
  • Question Question
Search a column for cell contents, in any order
Replies
3
Views
73
DanteAmor
DanteAmor
G
  • Question Question
VBA - add formula to cell when created
Replies
2
Views
528
Golaidron
G
M
  • Question Question
Countif function criteria
Replies
5
Views
372
mrsushi
M
C
  • Solved
VBA - Checking for Duplicates
Replies
2
Views
948
cwhaley1
C

Forum statistics

Threads
1,225,746
Messages
6,186,791
Members
453,371
Latest member
HMX180

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top