I have a spreadsheet that I use to track progress completion based on the percentage of checkboxes that are TRUE (checked).

The formula I use for this has 3 separate references to another sheet. I do this for every project and I am looking into reducing the amount of copy and pasting I am doing when adding a new tracker. Currently I am using the below formula:

=COUNTIF('Automation: PHP Reference Parser'!A:A,TRUE)/(COUNTIF('Automation: 
PHP Reference Parser'!A:A, FALSE)+COUNTIF('Automation: PHP Reference 
Parser'!A:A, TRUE))

I am trying to replace the three instances of 'Automation: PHP Reference Parser' with a reference to a cell that already contains this string as the project name. I have attempted to do this with INDIRECT but I get a parse error in the following attempt:

=COUNTIF(INDIRECT(A2)!A:A,TRUE)/(COUNTIF(INDIRECT(A2)!A:A, FALSE)+COUNTIF(INDIRECT(A2)!A:A, TRUE))
up vote 0 down vote accepted

Change your

INDIRECT(A2)!A:A

to the following format.

INDIRECT(A2&"!A:A")
  • This is exactly what I was looking for. Thank you! – Jason Dec 6 at 21:35

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.