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
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!