-1

I am trying to combine data from other sheets into a single sheet. I thought I could use a formula to find the last row in each sheet and combine with an Indirect ArrayFormula but that doesn't work.

Indirect("ARRAYFORMULA({'Sheet1'!A2:J"&ArrayFormula(MAX(IF(NOT(ISBLANK ('Sheet1'!A1:A10000)), ROW('Sheet1'!A1:A10000),0)))&";'Sheet2'!A2:J"&ArrayFormula(MAX(IF(NOT(ISBLANK ('Sheet2'!A1:A10000)), ROW('Sheet2'!A1:A10000),0)))})")

The Indirect and ArrayFormula combo doesn't seem to be the answer as it errors out - it appears you can't run an indirect and array formula together.

EDIT: Using the formula above without the Indirect and finding the last blank row will combine the sheets when I keep a long row range but it keeps all the blank rows and spreads the data of each sheet out too much.

ARRAYFORMULA({'Sheet1'!A2:J100";'Sheet2'!A2:J100)})

Which is why I was trying to use the max arrayformula to find the last row in each sheet to consolidate the blank rows.

ArrayFormula(MAX(IF(NOT(ISBLANK ('Sheet1'!A1:A10000)), ROW('Sheet1'!A1:A10000),0)))
Rubén
  • 34,714
  • 9
  • 70
  • 166
betwixt
  • 31
  • 7

3 Answers3

1

Since I misunderstood your question, here's an alternative to the formula you provided..

=QUERY({Sheet2!A:J; Sheet3!A:J}, "select * where Col1 <>'' ")
JPV
  • 26,499
  • 4
  • 33
  • 48
0

In a google spreadsehet, if you want to retrieve the last row of both sheets you can try:

={INDEX(Sheet1!A:J, MAX(FILTER(ROW(Sheet1!A:J),NOT(ISBLANK(Sheet1!A:A))))); INDEX(Sheet2!A:J, MAX(FILTER(ROW(Sheet2!A:J),NOT(ISBLANK(Sheet2!A:A)))))}

this will put the last row of the second sheet in a row under the last row of the first sheet. In case you want those rows to appear in the same row, try:

={INDEX(Sheet1!A:J, MAX(FILTER(ROW(Sheet1!A:J),NOT(ISBLANK(Sheet1!A:A))))), INDEX(Sheet2!A:J, MAX(FILTER(ROW(Sheet2!A:J),NOT(ISBLANK(Sheet2!A:A)))))}
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Thanks, that works for getting me the last row in each sheet. Looking to pull everything, so A2:J(last nonblank row) for each sheet. So it might be A2:J9 or A2:J1000 – betwixt Dec 24 '14 at 17:20
  • Why not use open ended ranges like I did in the formulas above ? – JPV Dec 25 '14 at 10:21
  • Using open ended on the original formula still pulls all the rows on the entire sheet (including blank ones). So if I have 1 header and 9 rows of data it pulls 900 blank rows before displaying the next sheet +headers – betwixt Dec 25 '14 at 17:18
0

I figured out you can't use indirect outside of an arrayformula but you can use it inside { }

 ={INDIRECT("'Sheet1'!A2:J"&MAX(FILTER(ROW(Sheet1!A:J),NOT(ISBLANK(Sheet1!A:A))))) ; INDIRECT("'Sheet2'!A2:J"&MAX(FILTER(ROW(Sheet2!A:J),NOT(ISBLANK(Sheet2!A:A)))))}

This combines the sheets perfectly.

betwixt
  • 31
  • 7