Index only visible filtered rows, but without ignoring blanks.

Hi, I have a 'dashboard' that lists names in a column.

I have a row that INDEXES this column, horizontally, such that the column is automatically 'transposed'.

The column, however, is controlled by multiple filters in adjacent columns.
I would like the INDEXING row to ignore hidden cells after applying a filter.
Secondly, in the original column, there are some necessary blanks, depending on the filter applied. I would like these blanks also replicated horizontally.

I did find one formula online that ignored hidden rows (I've lost the link) but unfortunately it also ignore blanks.

In summary, no matter how the column is arranged/filtered, I want that column transposed verbatim.

My example file is attached.

Hi,

Array formula** in D30:

=IFERROR(INDEX($E:$E,SMALL(IF(SUBTOTAL(3,OFFSET($D11,ROW(D11:D27)-MIN(ROW(D11:D27)),)),ROW(D11:D27)),COLUMNS($A:A))),"")

and copied across.

Note that this ignores filtered rows, though not hidden (you appear to mix your terminology above - I presume you mean to exclude only the former).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Originally Posted by byteroom

My column actually runs infinitely, so should I replace D11:D27 with D11:D1048576, or is there a more efficient replacement?

No! If you reference up to the very last column in the worksheet, you'll suffer the consequences in terms of calculation speed.

Either choose a suitably low, though sufficient, upper bound for the end row being referenced or, even better, makes your ranges dynamic, such that they automatically adjust as your data expands/contracts. I can show you how to do this if you wish.

Regards

Just out of curiosity, do you mean by making a table/named reference & linking the formula to it?

Anyway, I'm fine with setting a 1000 row limit to the array, it's more than enough for my purpose.

Thank you.

Originally Posted by byteroom

Just out of curiosity, do you mean by making a table/named reference & linking the formula to it?

Yes, sorry - formatting the source data as an Excel Table would work, and would require no further work on your part. If you prefer to use a non-structured table, you'll need to use Defined Names comprising formulas which auto-detect the last row containing data within that table. The latter requires a touch more work, which I am happy to show you if you wish.

Regards