This is another Xcelsius issue i had — but the solution is usable in any excel spreadsheet.
I wanted to create a fake drop down (custom image w/ little arrow like most web pages have now + push button + list box) that i could set up with dynamic options — dynamic meaning different options based on different states of the dashboard, not adding or removing new options at runtime. The problem was that i needed to link to a range in the Excel spreadsheet that gave me the currently available options and did not have blank spaces between them. I hunted on the internet and found an example, but it used functions that are not available in the Xcelsius-enabled list of Excel functions.
So I edited it to be Xcelsius-friendly and here it is (click on this pic to see a larger version):
- Column 1 (H): a row number that is copied across to column 4 when the option text is marked as “available” in column 3. these are hardcoded numbers. Xcelsius doesn’t support the ROW function.
- Column 2 (I): The table of data. In my case it doesn’t have spaces, but effectively does when i have certain options marked as “unavailable”.
- Column 3 (J): A formula that is unique for each row and determines if THAT row should be available. This checks the status of different things in the Xcelsius file (e.g., the dynamic visibility of other components in order to determine what tab on on, etc) and determines when this option should be available in my faked-out combo box implementation. If you have data that has spaces in it that you are removing, this row can check for that or (even better) just combined with column 4.
- Column 4 (K): A filter. If this row is “available”, then this cell is set equal to the row number in column 1.
- Column 5 (L): blank… i left it there for spacing, or maybe for adding something later… i don’t know. I can’t remember. but it is blank.
- Column 6 (M): The magic happens here… Keep reading
The function in Column 6 is this, the column letters are defined in the column definitions above (i.e., column 1 is H) and the first row of that table was at row 30:
To break this down:
- ISERROR(SMALL($K$30:$K$49,H30)),””…: The first part here checks if there is a next smallest value in column 4. if there is, it continues to the INDEX function, otherwise it leaves the cell in column 6 empty. Column K is the sparsely populated filter column (aka column 4), and the “n” in the “nth next smallest value” comes from column H (the rownum).
- INDEX($I$30:$I$49,SMALL($K$30:$K$49,H30)): Since we know an “nth next smallest value” exists in the filter column, this pulls the “option text” in column 2 (i.e., the table data) corresponding to that row number. the row number starts at 1 for the first row, so it is equal to the “index” of that row when using the INDEX function.
It is actually pretty simple and useful. I simplified it as i wrote it here because i found a function that I was using that was redundant, but in case it solved some other issue that i don’t have and can’t think of, here is it:
. The MATCH at the end there is redundant. In my example, the relative index is also equal to the “nth next smallest value” in the filter column — so i’m doing a MATCH using the SMALL function to get the same number the SMALL function already returns. It is useful to write these posts and fix my own code!