indirect formula question

[Chris]

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,
but if anyone cares to take a look at the excel file on this page.

http://spreadsheetpage.com/index.php/file/mail_merge_demo/

I think that I have to add an extra formula to =INDIRECT("Data!A"
&RowIndex) so that it only recognizes rows that are visible, but not
quite sure.

Any expert insight is greatly appreciated. Thanks,

Chris

 

[Harlan Grove]

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.

 

[Chris]

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.

Thanks for the info, I will explain a little more in detail:
My spreadsheet is steup like the following with a print macro that
handles the actual printing. The print macro runs through each row and
prints the StartRow through EndRow. When I apply an autofilter to my
'DataSheet' the RowIndex, StartRow and EndRow are still including the
hidden rows that were filtered out. My goal is to have the current
record of "3" as seen below to be the 3rd visible record after the
autofilter has been applied along with the StartRow of "2" and EndRow
of "7" to be the visible rows.

Total No. Records: 1357
Current Record: 3 <----- named range "RowIndex"
First Record to Print: 2 <----- named range "StartRow"
Last Record to Print: 7 <----- named range "EndRow"

These 3 cells below pull data from my 'DataSheet'
=INDIRECT(("PrintData!B"&RowIndex)) <----- named range "name1"
=INDIRECT(("PrintData!C"&RowIndex)) <----- named range "name2"
=INDIRECT(("PrintData!C"&RowIndex)) <----- named range "name3"

 

[Harlan Grove]

My spreadsheet is steup like the following with a print macro that
handles the actual printing. The print macro runs through each row and
prints the StartRow through EndRow. When I apply an autofilter to my
'DataSheet' the RowIndex, StartRow and EndRow are still including the
hidden rows that were filtered out. . . .

....

So you're printing rows in DataSheet using a macro?

Your print macro prints a row at a time? How? Excel's own printing is
based on pages, not individual lines. Do you mean you're using VBA to
write to text files, and when the text file output is completed, the
text file is sent to a printer?

. . . My goal is to have the current
record of "3" as seen below to be the 3rd visible record after the
autofilter has been applied along with the StartRow of "2" and EndRow
of "7" to be the visible rows.

Unclear. Do you want *record* 3 appearing above *rows* 2 and 7? Or do
you mean record 3 is the same as row 3 (or maybe row 4), and it'd
appear between rows 2 and 7? Or do you mean you want printed output
like

Total No. Records:      1357
Current Record: 3       <----- named range "RowIndex"
First Record to Print:  2       <----- named range "StartRow"
Last Record to Print:   7              <----- named range "EndRow"

(without the <--... bits), and records 2 and 7 are the first and last
visible rows in your actual data range, and the current record (in
this case 3) could be hidden or not?

Actually, you can use the technique from my previous response, but
with a few changes. Define the name TBL referring to your data range
EXCLUDING title/header rows. So it'd be define like =DataSheet!$A$3:$J
$1002. Then define TBL_VisibleRows referring to the formula

=IF(SUBTOTAL(3,OFFSET(TBL,ROW(TBL)-MIN(ROW(TBL)),0,1,1)),
ROW(TBL)-MIN(ROW(TBL))+1)

This evaluates to an array of either row indices for the visible rows
in TBL or FALSE for the invisible rows in TBL. The first record to
print would be given my

=INDEX(TBL,MIN(TBL_VisibleRows),0)

and the last record to print would be given my

=INDEX(TBL,MAX(TBL_VisibleRows),0)

As for the current record, how do you determine it? Is it record in
which the cell pointer is located? Is it a user entry? Is it
determined some other way?

 

[Chris]

...>My spreadsheet is steup like the following with a print macro that

...

So you're printing rows in DataSheet using a macro?


I am not actually printing the row on the datasheet, the data from the columns in the row populates other cells based on the named ranges.

Your print macro prints a row at a time? How? Excel's own printing is
based on pages, not individual lines. Do you mean you're using VBA to
write to text files, and when the text file output is completed, the
text file is sent to a printer?

Yes - the macro just spits out the number of pages I specify with startrow and endrow into another data form.


Unclear. Do you want *record* 3 appearing above *rows* 2 and 7? Or do
you mean record 3 is the same as row 3 (or maybe row 4), and it'd
appear between rows 2 and 7? Or do you mean you want printed output
like

Currently, record 3 is the same as row 3, but I want record 3 to be
the 3rd visible record on the datasheet, regardless of the actual row
number. after applying an autofilter, the 3rd visible record could be
the 10th actual row, for example,

(without the <--... bits), and records 2 and 7 are the first and last
visible rows in your actual data range, and the current record (in
this case 3) could be hidden or not?

Yes, the current record of 3 as it currently is could be hidden based
on the autofilter.

Actually, you can use the technique from my previous response, but
with a few changes. Define the name TBL referring to your data range
EXCLUDING title/header rows. So it'd be define like =DataSheet!$A$3:$J
$1002. Then define TBL_VisibleRows referring to the formula

=IF(SUBTOTAL(3,OFFSET(TBL,ROW(TBL)-MIN(ROW(TBL)),0,1,1)),
ROW(TBL)-MIN(ROW(TBL))+1)

This evaluates to an array of either row indices for the visible rows
in TBL or FALSE for the invisible rows in TBL. The first record to
print would be given my

=INDEX(TBL,MIN(TBL_VisibleRows),0)

and the last record to print would be given my

=INDEX(TBL,MAX(TBL_VisibleRows),0)

As for the current record, how do you determine it? Is it record in
which the cell pointer is located? Is it a user entry? Is it
determined some other way?

The current record is determined by the number I put. In the previous
example the current record of 3 is the third row on the data sheet. If
I changed that number to 4, it would be the fourth row and so on. The
whole issue is having the record numbers based on visible rows, not
actual row numbers as they currently are.

 

[Harlan Grove]

Currently, record 3 is the same as row 3, but I want record 3 to be
the 3rd visible record on the datasheet, regardless of the actual row
number. after applying an autofilter, the 3rd visible record could be
the 10th actual row, for example,

....

Here you say record 3 would be the 3rd visible record.

Just use the defined names from my previous response. Record 3 would
be given by the formula

=INDEX(TBL,SMALL(TBL_VisibleRows,3),0)

Yes, the current record of 3 as it currently is could be hidden based
on the autofilter.

Here you say record 3 could be visible or invisible. If you want the
3rd row of TBL whether it's visible/filtered or not, use

=INDEX(TBL,3,0)

If you want the record corresponding to your entry in a cell, say X99,
use

=INDEX(TBL,X99,0)

And back to case 1 at the top, if you want the kth visible row where k
is your entry in cell X99, use

=INDEX(TBL,SMALL(TBL_VisibleRows,X99),0)

 

[Chris]

...>Currently, record 3 is the same as row 3, but I want record 3 to be

...

Here you say record 3 would be the 3rd visible record.

Just use the defined names from my previous response. Record 3 would
be given by the formula

=INDEX(TBL,SMALL(TBL_VisibleRows,3),0)


Here you say record 3 could be visible or invisible. If you want the
3rd row of TBL whether it's visible/filtered or not, use

=INDEX(TBL,3,0)

If you want the record corresponding to your entry in a cell, say X99,
use

=INDEX(TBL,X99,0)

And back to case 1 at the top, if you want the kth visible row where k
is your entry in cell X99, use

=INDEX(TBL,SMALL(TBL_VisibleRows,X99),0)

Thanks for your help - I will see if I can get this to work.