Linking to a column on another sheet shows thousands of blank rows

New Contributor

Hi there!  I'm new so please pardon me if this is the wrong place to ask this. 

I need to link a column on one sheet to another so I simply used the following formula:

=IF(ISBLANK('REWORKED STYLE MASTER'!P:P),"",'REWORKED STYLE MASTER'!P:P)

 

"Reworked Style Master" is the name of the sheet I'm extracting the information from. 

 

The problem although the data is in a table that goes down to the 26,799th row, the linked column will shoot way past the bottom of the table and go to the 1,048,576th row on that spreadsheet. Is there a way to make the linked column only go down to the end of the table, therefore the 26,799th row? 

 

Thanks!

3 Replies

@Arcluna 

If you want to keep the blanks:

 

=IF('REWORKED STYLE MASTER'!P1:P26799="","",'REWORKED STYLE MASTER'!P1:P26799)

 

If you want to suppress the blanks, you can use the FILTER function:

 

=FILTER('REWORKED STYLE MASTER'!P1:P26799,'REWORKED STYLE MASTER'!P1:P26799<>"")

Thank you so much Hans! So simple... I must be tired...

@Arcluna 

As variant

=SUBSTITUTE( P1:XLOOKUP(TRUE, P:P<>"",P:P,,,-1), "", "" )