Search code examples
google-sheetsgoogle-sheets-formula

Auto-change or Query function Google sheets

I currently have a spreadsheet with a sheet called Data that automatically imports data and has manual data entry, which then formats it correctly in a sheet called Output. From there, it is distributed over multiple sheets through QUERY functions with formulas like this:

=QUERY(Output!$B$2:XY,"SELECT AB",0)

This works, but it brings problems when I add a new column. Total amount of columns is 70 at the moment (excluding column A which is Date). If I add a new column, for example after Z, so column AA, then all the query functions that have rows AA - beyond don't work anymore and have to be manually updated.

Is there a way to auto-change these columns, so I can add a column in data and output, without it requiring manual updating?

Please note that the Data sheet and Output sheet have identical columns but have different formatting (for example duration is in numbers, and in Output formatted with =IF(ISBLANK(Data!AG7), ,Data!AG7/24/3600). All sheets have column A being the date.

Small and edited example sheet (exact same layout, just less data: https://docs.google.com/spreadsheets/d/1wLJ1xX9-ZXfmf9NHsBl3GscVS35hHnm6vFawgxRsOMY/edit?usp=sharing

If in this sheet a row is added, say, after Duration, then column G - Q and the single Activity column will display the wrong data.

Solution

  • TL;DR

    You can use ARRAYFORMULA with HLOOKUP to get the values you need:

    =ARRAYFORMULA(IFERROR(HLOOKUP(C14:Q14, Output!B1:Q, ROW(A2:A), FALSE)))

    Full answer

    The main problem of your code is that you are manually selecting the import column for every single one. A nicer way of doing this is to use the header as a way of finding it.

    This answer intends to be a mini-tutorial on how it works. I’ll be using the linked spreadsheet to explain it, so all the ranges may need to be changed for other spreadsheets.

    Step 1: Using the column header to select the column in Output

    To do so, we’ll use HLOOKUP. This formula finds a column with the appropriate header (first row) and then returns the value with its column and the specified row.

    So if you go to C15 and add the formula:

    =HLOOKUP(C14, Output!B1:Q, 2, FALSE)

    You can see that the value on this cell becomes the first value of the Score column on the Output sheet. The parameters we added:

    • C14: The value we search, the header in this case.
    • Output!B1:Q: The range of values we want. In this case are the headers and the values.
    • 2: The index of the cell we want on the found column. Starts with 1 (headers).
    • FALSE: If the headers in Output are sorted. In this case, they are not.

    Step 2: Extend to every row in the C column

    Now that we have it working for a single cell, let’s extend it to multiple cells. To do so we’ll use ARRAYFORMULA together with ROW:

    =ARRAYFORMULA(HLOOKUP(C14, Output!B1:Q, ROW(A2:A), FALSE))

    What ARRAYFORMULA allows us to do is to set a function in a single cell but fill multiple cells. In this case we are extending it to the entire column.

    But now you will realize that we need to change the index each time, that is the 2 that we had before needs to be different for every cell.

    Here is where ROW comes into play: it returns the row number of the given cell. But we can actually ask for any cell so we start with A2 (so it returns 2) and continue all it can. Together with ARRAYFORMULA it generates an array of numbers from 2 to the number of rows of the document.

    Step 3: Repeat for each column

    ARRAYFORMULA does not only work in one dimension. So we can extend it to other columns. Changing C14 for the range of columns is enough:

    =ARRAYFORMULA(HLOOKUP(C14:Q14, Output!B1:Q, ROW(A2:A), FALSE))

    Step 4: Filter errors

    Now you’ll see some #REF errors. They are caused by the header not actually existing in the Output sheet. We can remove the errors with IFERROR:

    =ARRAYFORMULA(IFERROR(HLOOKUP(C14:Q14, Output!B1:Q, ROW(A2:A), FALSE)))

    Notice though that this forces you to have the exact same name everywhere which you may not want.

    Step 5: Modify to your needs

    This formula is the simplest I could think of, but it still needs to be maintained and tailored to where it goes. You can basically modify 2 things in the formula:

    • C14:Q14: This range is the value to compare to generate the columns. We are using the headers but it doesn’t need to be. You can use another row even from another sheet. This could be done to have the headers with different names.
    • Output!B1:Q This range is all the data. You’ll need to change it if you add a new row.

    Notice that you cannot manually add values in the columns in the first range, so multiple formulas may actually be necessary. You only need to change the first range.

    You can improve this formula and add more features to it, but it can get complex pretty quickly.

    References