Chris said:
Hello - I have been trying to wrap my head around getting an indirect
formula that indexes rows to be printed to only recognize visible rows
so I can run a filter.
It's a little difficult to explain with my limited excel knowledge,
...
Try anyway. You don't need to try to use correct terminology. Just
describe what you're trying to do as clearly as possible.
You want to print only filtered rows? Excel already provides that
functionality - apply the filter and print, Excel will print only the
visible rows.
Are you trying to use formulas to reproduce the visible rows in some
other range/worksheet? Obvious first question would be WHY?
Can it be done? Yes, but it's not simple. First, create a defined name
like TBL referring to your original table, the one your're going to
filter and INCLUDE any header rows. Then create another defined name
like TBL_VisibleRows defined by the formula
=IF(SUBTOTAL(3,OFFSET(TBL,ROW(TBL)-MIN(ROW(TBL)),0,1,1)),ROW(TBL)-
MIN(ROW(TBL))+1)
Then in the top row of the range in which you want to duplicate the
visible rows from TBL, select the appropriate number of columns. For
example, if TBL spanned 8 columns and you want the results to appear
beginning in cell A1 in another worksheet, select A1:H1 in that other
worksheet, type the formula
=INDEX(TBL,SMALL(TBL_VisibleRows,ROWS(A$1:A1)),0)
hold down [Ctrl] and [Shift] keys and press [Enter]. This will enter
the first visible row from TBL as an array formula in A1:H1 of the
worksheet where you're duplicating the visible rows. Drag A1:H1 down
as far as needed. The formulas will return #NUM! when you've exhausted
the visible rows from TBL.
If this isn't what you mean, then YOU need to provide a more complete
explanation WITHOUT making anyone else hunt for some example
spreadsheet somewhere on the web. Anyone smart enough to answer your
question would also be smart enough to know NOT to open some
stranger's spreadsheet. The quality of responses you'll get usually
depends on the precision and completeness of YOUR description of the
problem. The less time you spend composing your questions, the more
general/less useful your responses are likely to be.