How can I use the Index function to find the last specific values in a column?
Here is the formula I started with: =IF([Hide Level 1]37 = "N", "", IF(INDEX(Format$1:Format36, COUNT(Format$1:Format36) + 0) = "W", "B", IF(INDEX(Format$1:Format36, COUNT(Format$1:Format36) + 0) = "B", "W")))
I am trying to index the Format column to find the last "B" or "W" so the formula will result in a "W" if the previous result was a "B" or a "B" if the previous result was a "W" while ignoring the blanks when Hide Level 1 is "N"
The blanks are throwing the formula off. Row 43 should have "B" in the Format column then a "W" in the Format column on row 44.
The idea is to get the formula to result in alternating "B's" and "W's" in the format column, use conditional formatting, filter, and achieve a banded row look.
Any help on this would be greatly appreciated.
Answers
-
Figured it out. Here's the formula in case anyone else might need something similar: =IF([Hide Level 1]37 = "N", "", IF(INDEX(Format$1:Format36, MATCH(MAX(COLLECT([Row ID]$1:[Row ID]36, Format$1:Format36, ISTEXT(@cell))), [Row ID]$1:[Row ID]36, 0)) = "B", "W", IF(INDEX(Format$1:Format36, MATCH(MAX(COLLECT([Row ID]$1:[Row ID]36, Format$1:Format36, ISTEXT(@cell))), [Row ID]$1:[Row ID]36, 0)) = "W", "B")))
This also works when refreshing linked data old and new then sharing a filter.
*change range and specific values for fit
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!