How to link a cell from another sheet based on multiple criteria?
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?
Best Answer
-
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)
Answers
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!