Google Sheets – How to Skip Blank Cells When Copying a Range by Formula

formulasgoogle sheets

I have three levels of hierarchical data in Google Spreadsheets that looks something like this:

Three level hierarchical data

As data is being entered in here, I want it collect in three separate sheets like this:

Level 1 Data Level 2 Data

The solution I am looking for is a formula driven approach. I have tried VLOOKUP but can't seem to figure out how to make it work for this situation. I have googled quite a bit and all I am getting is applying filters, or removing blank cells like this one, but that does not serve my purpose.

Best Answer

Filter() should do that. Let's say the sheet with the 'source data' is called Sheet1, then on the second sheet (where you want the level1 names), in cell A1 try:

=filter(Sheet1!A:A; len(Sheet1!A:A))

and repeat for all the other sheets and ranges..

Related Solutions

Google Sheets – Filter Based on Data Validation Dropdown

Okay so I solved this problem with this function:

=sum(filter(J6:J25,K6:K25="Meals",month(B6:B27)=7))

This would give me the subtotal for all Meal expenses for the month of July. This is a bit of a pain to apply when referencing this data in other sheets, but it works.

Google-sheets – Return cell reference containing closest value

=VLOOKUP (a1,range,2) 
The problem with that is if the value isn't listed, which most of the time it isn't, it will fail. I want to return the closest value, which VLOOKUP doesn't appear to do

First, sort range in ascending order. You could do in place or use SORT

Try

=VLOOKUP(a1,SORT(range,1,TRUE),2)

Best Answer

Related Solutions

Google Sheets – Filter Based on Data Validation Dropdown

Google-sheets – Return cell reference containing closest value

Related Topic