So I have an excel spreadsheet where I will copy data into. Then on that sheet I will have several other formulas combining text and so on. On a second sheet I want to take all the rows and certain columns and display those items.
I have used the the Take formula, to get all the fields. This works but in the array part of the function it pulls all of the blank cells. When I copy the data in the amount of rows may differ.
For example today I might have 15 rows of data. Tomorrow it may be 25. I do know it will never be more than 50. But I want to capture only the rows with data. Right now it will simply put 0's for the blank cells. This will cause other issues when I import the data into out other software.
This is a small sample set. There are typcially 15-30 rows with data. The formula I use is: =TAKE(Destination!A1:G50,,3) this then fills 3 columns and 50 rows with data. On the second sheet I get the following result:
As you can see the next 3+ rows have all zeros. The reason I have G50 is because the amount of data varies daily but will never exceed 50.
So I ended up finding a work around after I understood the formula and how to do what I wanted.
I basically did a count on all the rows that had a value. On a separate sheet I stored that count. Then when I did the TAKE formula I just chose the count and used that as the rows argument.