Search code examples
arraysgoogle-sheetssyntaxrangegoogle-sheets-formula

Auto-increment row number from another sheet in INDIRECT formula

I'm working in a google spreadsheet with multiple sheets. This is a situation where I can mess around in a sheet (I call it Sandbox) that I make but can't do anything in another sheet (DeWalt) that someone else created and maintains. So I copy/paste/pull data from the off-limits sheet (DeWalt) into mine...

Here's what I've been trying to do... Use an INDIRECT() to pull and rearrange data from certain parts and certain columns from the DeWalt sheet, into my own sheet, using this.

={INDIRECT("DeWalt!$E"&$K$1), INDIRECT("DeWalt!$B"&$K$1), INDIRECT("DeWalt!$A"&$K$1)}

This is in A1 and fills out a row of 3 cells(cols). It works great, 1 time. But if I want to drag and autofill down...

The part that's not working is the $K$1 reference. This is a cell in my sheet where I want to put the row # that I want the formula to use in all 3 sheet/cell references is uses (it has hundreds of rows... I might want to start at 500 or row 750, etc) I would like to enter a # once, in that cell in my sheet, and then pull/autofill the formula in cell A1 down as many rows as I want, and have the formula auto-increment the row #s from the other sheet(DeWalt), using the # I enter in K1 as its starting reference. Why not use FILTER? Because after autofilling, I want to be able to insert some rows within my returned data, anywhere, at will; so FILTER'S overwriting error would be an issue.

And yes, I've read similar situations and their answers... I've tried ROW()... CELL("address"...) etc and couldn't seem to retrofit any of those for my particular situation. So, you guys have come through when I had spreadsheet questions before and I believe you can again!

Solution

  • try:

    ={INDIRECT("DeWalt!E"&K1), INDIRECT("DeWalt!B"&K1), INDIRECT("DeWalt!A"&K1)}

    $ in double-quotes is pointless

    $ before K is pointless if you drag your formula downwards only

    $ before 1 will freeze it to 1 so after dragging you will always get K1

    hard to tell what do you want... maybe this:

    ={INDIRECT("DeWalt!E"&K$1+ROW(K1)-1), 
  INDIRECT("DeWalt!B"&K$1+ROW(K1)-1), 
  INDIRECT("DeWalt!A"&K$1+ROW(K1)-1)}

    update:

    ={INDIRECT("Sheet1!A"&E$2+ROW(E1)-1),
  INDIRECT("Sheet1!B"&E$2+ROW(E1)-1), 
  INDIRECT("Sheet1!I"&E$2+ROW(E1)-1)}

    enter image description here