How to link a cell from another sheet based on multiple criteria?

KarenTF

I have a metric sheet created and formatted in a specific way to achieve the visual display desired from a column graph. What I'd like to do is be able to cell-link the Total Points from the main sheet to the metric sheet based on 2 criteria: Current Month and Location. In the main sheet, I've created a helper column with the checkbox to account for the Current Month, which is working and checked when an entry from the current month occurs. How can I cell link the current month Total Point score for a specific location? That way, I can automatically pull the current month's score per site into my pre-formatted metric sheet, so it will display my chart correctly on my dashboard. And after the month passes, the unchecking of the box will automatically remove the values, or at least just get replaced when the following month's score per location is entered.

So far, I've tried this and it is not working:

=INDEX(COLLECT({Main Sheet Total Points Range 1}, {Main Sheet Location Range 2},

"Location 1", {Main Sheet Current Month Checkbox Range 3}, "1"))

Am I on the right track?

Paul Newcome

You need to finish out the INDEX function:

=INDEX(COLLECT({Main Sheet Total Points Range 1}, {Main Sheet Location Range 2},"Location 1", {Main Sheet Current Month Checkbox Range 3}, "1"), 1)

Paul Newcome

You need to finish out the INDEX function:

=INDEX(COLLECT({Main Sheet Total Points Range 1}, {Main Sheet Location Range 2},"Location 1", {Main Sheet Current Month Checkbox Range 3}, "1"), 1)