Need assistance with a google sheets formula (incrementing issue)

Question

I'm using this formula to ref from one google sheet to another. =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1HKmUMM4vLvNt9Z20Yc_VyrDx25MxlDQlJjTAXtHl-p4/edit#gid=397776635", "012!D1")

I need the part that says 012 to increment, but I need the D1 part to stay the same. Any suggestions?

TIA

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1HKmUMM4vLvNt9Z20Yc_VyrDx25MxlDQlJjTAXtHl-p4/edit#gid=397776635", "012!D1")

doubleunary · Answer 1 · 2022-12-14T16:57:07.817

To import the same range from many tabs in a source spreadsheet, and flatten those ranges into one row each, use reduce(), flatten() and transpose(), like this:

=lambda( 
  sheetNames, rangeA1, header, 
  reduce( 
    header, sheetNames, 
    lambda( 
      result, sheetName, 
      { 
        result; 
        iferror( 
          transpose( flatten( 
            importrange( 
              "1HKmUMM4vLvNt9Z20Yc_VyrDx25MxlDQlJjTAXtHl-p4", 
              sheetName & "!" & rangeA1 
            ) 
          ) ), 
          header 
        ) 
      } 
    ) 
  )
)( 
  { "001", "002", "003" }, 
  "D4:D8", 
  { "Dress name 1", "Amour Code", "Size", "Colour", "Price" } 
)

You can replace { "001", "002", "003" } with another expression that produces an array of sheet names. The number of values in header must match the number of columns in rangeA1.

Thank you - the D part isn't the issue, that is staying the same, but I need the 012 to automatically move to the next number 013 then 014 in the formula in the next row (hope that makes sense :) — Nats79, Dec 13 '22 at 14:41

score 0 · Answer 2 · answered Dec 13 '22 at 15:03

0

Is "012" the name of the sheet? You can do something like this:

=BYROW(INDEX(text(sequence(20,1,12,1),"000")),LAMBDA(each,Importrange("1HKmUMM4vLvNt9Z20Yc_VyrDx25MxlDQlJjTAXtHl-p4",each&"!D1")))

Change BYROW with BYCOL if you want it horizontally. Change 20 with the amount of sheets you need to grab and 12 to your first sheet (I've put 12 because it was your example).

PS: It works when you only import one cell

answered Dec 13 '22 at 15:03

Martín

7,849
2
3
13

Thank you. 012 is the name of a sheet, then 013 is the next sheet etc...it starts from 001 and goes up to 100 so I need each row to reflect the information from each sheet. (various columns) – Nats79 Dec 14 '22 at 12:23
Then Try =BYCOL(INDEX(text(sequence(89,1,12,1),"000")),LAMBDA(each,Importrange("1HKmUMM4vLvNt9Z20Yc_VyrDx25MxlDQlJjTAXtHl-p4",each&"!D1"))) – Martín Dec 14 '22 at 12:38

score 0 · Answer 3 · answered Dec 14 '22 at 16:47

0

try this and drag down the formula:

=IFERROR(LAMBDA(tab,Importrange("1HKmUMM4vLvNt9Z20Yc_VyrDx25MxlDQlJjTAXtHl-p4",tab&"!D1"))(text(row(A1),"000")),(text(row(A1),"000"))&" :NO TAB FOUND")

-

answered Dec 14 '22 at 16:47

rockinfreakshow

15,077
3
12
19

Need assistance with a google sheets formula (incrementing issue)

3 Answers3