-1

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.

Atmo
  • 2,281
  • 1
  • 2
  • 21
arun
  • 4,595
  • 5
  • 19
  • 39
  • What is the Excel version you are working with? Please tag it as it could have an impact on the answer. – Atmo Apr 12 '23 at 07:44
  • @Atmo, Microsoft® Excel® for Microsoft 365 MSO (Version 2303 Build 16.0.16227.20202) 64-bit – arun Apr 12 '23 at 07:47

1 Answers1

1

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)
Atmo
  • 2,281
  • 1
  • 2
  • 21