Automatically extending a column data without adding zeros

Copper Contributor

I have created a workbook with two sheets.  My first sheet contains raw reporting data.  The second sheet is used to automatically filter the columns I care about.  On a daily basis, I will copy and paste new raw data into sheet 1, with sheet 2 automatically filtering the raw data. 

 

I created the following simple function in the second sheet:

 

=('Raw Data'!C1:C100)

 

So it properly imports everything from C1 and below until I get to no content and it automatically adds zeros at the end.  For example, if I only had 45 column data cells in column C then 46 to 100 will populate with 0.

 

How do I write this function to say populate C1 to whenever the data stops?

1 Reply

@jacobpeper 

 

I do something similar with this function bringing in data from a separate workbook. The FILTER function does require the most recent version of Excel.

 

=FILTER([Source.xls]Worksheet1!$A$5:$T$100,[Source.xls]Worksheet1!$A$5:$A$100<>"")

 

It pulls in from Source.xls all rows and columns (up to column T) that have something in column A, stops when it reaches blank cells. 

 

Here's a good video that explains the FILTER function if you're not familiar with it. 

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...