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

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

Hi Steve

Try the below

=COUNTIFS(INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!C:C"),E10,
INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!M:M"),G10,
INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!H:H"),$D$5)

--
Jacob (MVP - Excel)


"Steve" wrote:

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

Worked a dream!! thank you very much!! - much much apprecaited

"Jacob Skaria" wrote:

Hi Steve

Try the below

=COUNTIFS(INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!C:C"),E10,
INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!M:M"),G10,
INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!H:H"),$D$5)

--
Jacob (MVP - Excel)


"Steve" wrote:

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

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



.