Help Needed with Dynamic CountIF
Some reason just returned #value ??? but Jacobs idea worked fine so thanks
anyway!
"Bob Phillips" wrote:
No need for VBA
=COUNTIFS(INDIRECT("'"&D5&"'!C:C"),E10,INDIRECT("' "&D5&"'!M:M"),G10,INDIRECT("'"&D5&"'!H:H"),$D$ 5)
--
HTH
Bob
"Steve" wrote in message
...
Hello All.
I have a sheet like this on a tab called Summary:
Month: Jan'10 (This is a drop down list showing all months)
Sales Person New Clients Sec Lead Source Minutes
SB GR Gen Pros
SB SB DB Pros
SB SB Pros
JS N/A
I have 12 additional tabs (Jan'10, Feb'10, Mar'10..etc etc etc)
What I am trying to achieve is a count of some data from the sheets on the
other tabs. This is what i have so far:
=COUNTIFS('Jan''10'!C:C,E10,'Jan''10'!M:M,G10,'Jan ''10'!H:H,$D$5)
FYI D5 (at the end) refers to the drop down month field as above. You will
note that the formula above only refers to the tab 'Jan'10, what i need is
for the tab reference to be dynamic based on what the user selects in D5,
so
for example, if the D5 = Feb'10 then the formula would be:
=COUNTIFS('Feb''10'!C:C,E10,'Feb''10'!M:M,G10,'Feb ''10'!H:H,$D$5)
I have a good level of skill in excel but nothing with VB so any ideas
would
be much appreciated.
Steve
.
|