Search code examples
excelexcel-365

Excel, Load Sheet1 data to Sheet2, if Sheet1 rows increase Sheet2 rows should increase to show those data

I have Sheet1 connected to ODBC and loads data from mysql server.

Now I have Sheet2, it needs to populate all the values (rows & columns from Sheet1).

If Sheet1 refreshed from connection its rows will change, so now i want Sheet2 will have those refreshed rows as well.

Already tried: I dont want to put like this =Sheet1!C13, because i dont know how many rows will Sheet1 have.

Solution

  • In Excel 365, you can work with spilled ranges.

    The base formula you have to type uses the MAX(ROW(...))/OFFSET trick to return a dynamic (spilled) range and would be something like (to type in Sheet2!C1):

    =LET(MaxRow, MAX(IF(NOT(ISBLANK(Sheet1!C:C)),ROW(Sheet1!C:C))), OFFSET(Sheet1!C:C,0,0,MaxRow))

    Note that you can work with more than just column C. Based on the above formula, we change the last part to be (using a 2-column contiguous range and the HSTACK function to add a third separate column):

    LET(MaxRow, MAX(IF(NOT(ISBLANK(Sheet1!C:C)),ROW(Sheet1!C:C))), HSTACK(OFFSET(Sheet1!C:D,0,0,MaxRow), OFFSET(Sheet1!F:F,0,0,MaxRow)))

    For formulas that use the resulting range, you will have to learn about the spilled range operator.

    In Sheet2:F1, type:

    ="I test the spilled range operator with " & C1#

    If you used the version of the formula returning 3 columns, you will see C1# "spills" on 3 columns too. This is easily solved by using the INDEX function like so (the 2 consecutive commas are not a mistake):

    ="I test the spilled range operator with " & INDEX(C1#,,1)