COUNTIF & INDIRECT

  • Thread starter Thread starter Jeff J
  • Start date Start date
J

Jeff J

I'm trying to capture data for reporting purposes from
multiple spreadsheets residing in separate files
(Workbooks).

I've tried setting up the formula so we could enter the
file name, sheet name, starting cell and ending cell on
the main "reporting" spreadsheet. Using INDIRECT, these
values would be used as part of the COUNTIF to tabulate
the values from each of the spreadsheets. e.g. Count the
number of U's in TestSheet located in file
Testworkbook.xls in column c3 through c200.

B11 - C:\Testworkbook.xls
B12 - Testsheet
B13 - C3 (starting cell)
B14 - C200 (end cell)

The formula:
=COUNTIF(INDIRECT
("'["&b11&"]"&b12&"'!"(INDIRECT&b13&":"&b14)),"U")

Is this feasible in Excel, or do I need to find another
process for achieving my goal?

Thanks,
Jeff
 
Jeff,

That should work, but the problem is that INDIRECT requires that the
workbook be open.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
I have the workbook open, which for us isn't a big deal.
It's better than loading all the spreadsheets into one
workbook. Anyhow, I keep getting an error on the
formula. When I remove the quote from the formula, there
error box pops up, and when after I hit return, the whole
formula is highlighted. I've played around with the
placement of quotes and parentheses with no success.

Jeff


-----Original Message-----
Jeff,

That should work, but the problem is that INDIRECT requires that the
workbook be open.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



I'm trying to capture data for reporting purposes from
multiple spreadsheets residing in separate files
(Workbooks).

I've tried setting up the formula so we could enter the
file name, sheet name, starting cell and ending cell on
the main "reporting" spreadsheet. Using INDIRECT, these
values would be used as part of the COUNTIF to tabulate
the values from each of the spreadsheets. e.g. Count the
number of U's in TestSheet located in file
Testworkbook.xls in column c3 through c200.

B11 - C:\Testworkbook.xls
B12 - Testsheet
B13 - C3 (starting cell)
B14 - C200 (end cell)

The formula:
=COUNTIF(INDIRECT
("'["&b11&"]"&b12&"'!"(INDIRECT&b13&":"&b14)),"U")

Is this feasible in Excel, or do I need to find another
process for achieving my goal?

Thanks,
Jeff
Click to expand...


.
Click to expand...
 
Jeff,

Try

=COUNTIF(INDIRECT("'["&B11&"]"&B12&"'!"&B13&":"&B14),"U")

Note that B11 should contain only the file name (testbook.xls) rather than
the complete path and file name.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)




I have the workbook open, which for us isn't a big deal.
It's better than loading all the spreadsheets into one
workbook. Anyhow, I keep getting an error on the
formula. When I remove the quote from the formula, there
error box pops up, and when after I hit return, the whole
formula is highlighted. I've played around with the
placement of quotes and parentheses with no success.

Jeff


-----Original Message-----
Jeff,

That should work, but the problem is that INDIRECT requires that the
workbook be open.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



I'm trying to capture data for reporting purposes from
multiple spreadsheets residing in separate files
(Workbooks).

I've tried setting up the formula so we could enter the
file name, sheet name, starting cell and ending cell on
the main "reporting" spreadsheet. Using INDIRECT, these
values would be used as part of the COUNTIF to tabulate
the values from each of the spreadsheets. e.g. Count the
number of U's in TestSheet located in file
Testworkbook.xls in column c3 through c200.

B11 - C:\Testworkbook.xls
B12 - Testsheet
B13 - C3 (starting cell)
B14 - C200 (end cell)

The formula:
=COUNTIF(INDIRECT
("'["&b11&"]"&b12&"'!"(INDIRECT&b13&":"&b14)),"U")

Is this feasible in Excel, or do I need to find another
process for achieving my goal?

Thanks,
Jeff
Click to expand...


.
Click to expand...
Click to expand...
 
Bingo, that did the trick!

Thanks!
Jeff

-----Original Message-----
Jeff,

Try

=COUNTIF(INDIRECT("'["&B11&"]"&B12&"'!"&B13&":"&B14),"U")

Note that B11 should contain only the file name (testbook.xls) rather than
the complete path and file name.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)




I have the workbook open, which for us isn't a big deal.
It's better than loading all the spreadsheets into one
workbook. Anyhow, I keep getting an error on the
formula. When I remove the quote from the formula, there
error box pops up, and when after I hit return, the whole
formula is highlighted. I've played around with the
placement of quotes and parentheses with no success.

Jeff


-----Original Message-----
Jeff,

That should work, but the problem is that INDIRECT requires that the
workbook be open.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



"Jeff J" <[email protected]> wrote
Click to expand...
in
message
I'm trying to capture data for reporting purposes from
multiple spreadsheets residing in separate files
(Workbooks).

I've tried setting up the formula so we could enter the
file name, sheet name, starting cell and ending cell on
the main "reporting" spreadsheet. Using INDIRECT, these
values would be used as part of the COUNTIF to tabulate
the values from each of the spreadsheets. e.g.
Click to expand...
Count
the
number of U's in TestSheet located in file
Testworkbook.xls in column c3 through c200.

B11 - C:\Testworkbook.xls
B12 - Testsheet
B13 - C3 (starting cell)
B14 - C200 (end cell)

The formula:
=COUNTIF(INDIRECT
("'["&b11&"]"&b12&"'!"(INDIRECT&b13&":"&b14)),"U")

Is this feasible in Excel, or do I need to find another
process for achieving my goal?

Thanks,
Jeff



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top