Cross-sheet reference enhancement

mkilroy

As a work around to the 100 cross-sheet references limit, I would like to propose that a cross-sheet reference can include multiple columns, but then you have the ability to select which of those columns you want to use in your formula.

To provide some context, I’ve created Capacity and Allocation (C/A) templates for 12 functional groups. In each template, the functional managers will enter in who is available to work on ‘x’ Project. I’m attempting to pull that information in from each C/A template using SUMIFS formulas. However, since each month, quarter, and half year requires a cross-sheet reference (about 20 total, including the Department and Project ID cross-sheet references), I was only able to pull in data for 5 of the 12 functional teams.

The formulas look something like this:

=SUMIFS({CA - PPM Jan 23}, {CA - PPM Department}, Department@row, {CA - PPM Project ID}, [Project ID]@row)

=SUMIFS({CA - PPM Feb 23}, {CA - PPM Department}, Department@row, {CA - PPM Project ID}, [Project ID]@row)

=SUMIFS({CA - PPM Mar 23}, {CA - PPM Department}, Department@row, {CA - PPM Project ID}, [Project ID]@row)

I’m curious if there is a way to reference the entire timeline (Jan 23 to H2 25) in a cross-sheet reference, but only sum the numbers from a specific month.

Thank you!