Function Help - Sheet References

jay.rathbun

Hi there - 

I am working on building a slightly better project tracker for our organization. We currently utilize calculated fields that we have to define for each new project, and sometimes the project does not have enough project rows to account for all items we are tracking. 

I need help with a specific formula that would reference values from another sheet. My current formula when the data is embedded in the same sheet is:

=COUNTIF(CHILDREN([Project Type]:[Project Type]), "Process Improvement")

I would like to pull these calculations into their own sheet, so what I am hoping to learn how to do is essentially:

=COUNTIF(CHILDREN({External Sheet}[Project Type]:{External Sheet}[Project Type]), "Process Improvement")

I cannot figure out how to do this with the documentation i have looked at. Any help would be greatly appreciated!

Paul Newcome

Unfortunately you cannot use hierarchy functions in cross sheet references, but feel free to submit a Product Enhancement Request.

 

In the mean time...

 

Helper columns may be able to work for you depending on your exact setup. It would be much easier to explain if you could provide some screenshots with sensitive/confidential information removed, blocked, or replaced with "dummy data".

 

The basic idea would be to use a helper column that essentially duplicates the data in the parent row and then include the helper column criteria in your COUNTIFS.