attempt to reference another worksheet in a countif formula opening up documents folder

[snipescc]

I everybody. I'm having an odd problem. I have a large spreadsheet with lost of tabs, one for each employee. I'm using Windows 7 and Excel 2010.

We need to get totals of what they have done for the week, organized by teams. But the teams change on a fairly regular basis. My plan was that someone trying to pull a report would put the employee's code in one cell, and that code would be put into a countif statement that would tell excel to pull the counts from that employee's tab.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]count
[/TD]
[/TR]
[TR]
[TD]Scottr
[/TD]
[TD]=COUNTIF('a2'!AU:AU,TRUE)
[/TD]
[/TR]
[TR]
[TD]Benj
[/TD]
[TD]=COUNTIF('a3'!AU:AU,TRUE)
[/TD]
[/TR]
</tbody>[/TABLE]

The idea is that people could just type in names where the teams are that week, and they wouldn't need me to redo things. Good in theory.

But when I type in the formula, and hit enter, it opens up the documents folder. And when I close that it gives me the #Value! error. My first plan had been to use a vlookup inside the countif statement, but when that didn't work i tried to simplify it by just having it reference the cell to the left. Anyone ever had a formula open up the documents folder before, or have a good idea oh how I can have a formula that uses another cell to reference what worksheet it should look at?

Thank you very much.

 

[Aladin Akyurek]

I everybody. I'm having an odd problem. I have a large spreadsheet with lost of tabs, one for each employee. I'm using Windows 7 and Excel 2010.

We need to get totals of what they have done for the week, organized by teams. But the teams change on a fairly regular basis. My plan was that someone trying to pull a report would put the employee's code in one cell, and that code would be put into a countif statement that would tell excel to pull the counts from that employee's tab.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]count
[/TD]
[/TR]
[TR]
[TD]Scottr
[/TD]
[TD]=COUNTIF('a2'!AU:AU,TRUE)
[/TD]
[/TR]
[TR]
[TD]Benj
[/TD]
[TD]=COUNTIF('a3'!AU:AU,TRUE)
[/TD]
[/TR]
</tbody>[/TABLE]

The idea is that people could just type in names where the teams are that week, and they wouldn't need me to redo things. Good in theory.

But when I type in the formula, and hit enter, it opens up the documents folder. And when I close that it gives me the #Value! error. My first plan had been to use a vlookup inside the countif statement, but when that didn't work i tried to simplify it by just having it reference the cell to the left. Anyone ever had a formula open up the documents folder before, or have a good idea oh how I can have a formula that uses another cell to reference what worksheet it should look at?

Thank you very much.

If 'a2' is in fact cell A2, with a sheet name in it, you'll need:

=COUNTIF(INDIRECT("'"&A2&"'!AU:AU"),TRUE)

 

[snipescc]

Woo Hoo, it worked! Thank you very much!

 

[Aladin Akyurek]

Woo Hoo, it worked! Thank you very much!

You are welcome. Thanks for providing feedback.

 

[snipescc]

You are welcome. Thanks for providing feedback.

I would have done it sooner, but was out of the office all Wednesday so i couldn't test it.