0

I am working on a sheet that tracks yearly budgets to review/see trends in the data. I am trying to make it user friendly and essentially "dummy-proof" for lack of a better word.

Most of the data is being imported from a different document for each year and I am asking the user to paste the link to that document in a different tab of this sheet. So my import function looks like this:

=IMPORTRANGE(Setup!$D19,"Annual Summary!P14:P57")

My problem is, the D19 reference is not changing as I need it to when I copy/paste or drag the formula. In the main data display tab I have each year in columns. For example, in Summary!F13 I have the following formula:

=IMPORTRANGE(Setup!$D19,"Annual Summary!P14:P57")

When I copy/paste or drag it into Summary!G13 I get the exact same thing: =IMPORTRANGE(Setup!$D19,"Annual Summary!P14:P57")

but I need this instead: =IMPORTRANGE(Setup!$D20,"Annual Summary!P14:P57")

I can drag the formula down the column and the cell reference changes on each row like you'd expect - if I bring it to Summary!F14 I get =IMPORTRANGE(Setup!$D20,"Annual Summary!P14:P57"). But I need this result in Summary!G13, not Summary!F14.

My question is, how do I make it so D19 to changes to D20, D21, etc. when I drag it to an adjacent column in the same row?

8
  • Lots of words but hard to follow. edit your question to include a few simple examples of the result you are getting and what you want. eg. I have =IMPORTRANGE(Setup!$D19,"Annual Summary!$P$15") in cell Sheet!xA1 I paste it in to Sheet!yA1 and I get =IMPORTRANGE(Setup!$D19,"Annual Summary!$P$15") but I want =IMPORTRANGE(dontsetup!$F72,"Monthly Summary!$P$15")
    – Blindspots
    Mar 20 at 18:54
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Mar 20 at 18:54
  • 1
    Is there a reason why you are you importing the data one cell at a time? The importrange() function is usually applied with a larger range reference to bring in all the necessary data in one go, often in a separate 'Import' tab, so that the data is easy to reference elsewhere in the target spreadsheet. Mar 20 at 18:57
  • Also sounds like you are transposing data from column to a row. Might be best to make a question with the main use case XY Problem as you are MacGuyvering stuff that may not need to be.
    – Blindspots
    Mar 20 at 19:14
  • 1
    @Blindspots thanks for the info. Tried clarifying things a bit in my OP. At minimum I need a new IMPORTRANGE function for each yearly sheet being imported by the user - after doubleunary's suggestion it is now just 1 import function for each year being tracked.
    – cotimms
    Mar 20 at 19:25

1 Answer 1

1

=IMPORTRANGE(Setup!$D19,"Annual Summary!P14:P57")

When I copy/paste or drag it into Summary!G13 I get the exact same thing: =IMPORTRANGE(Setup!$D19,"Annual Summary!P14:P57")

but I need this instead: =IMPORTRANGE(Setup!$D20,"Annual Summary!P14:P57")

You currently have sheet IDs in the column Setup!D19:D22.

To make those references work the way you want, place the sheet IDs in the row Setup!D19:G19.

You should also remove the remaining $ in the importrange() formula.

1
  • I was hoping to avoid that to have a better UX - previously I did have the sheet IDs in the same row on the main summary tab, but felt they were better suited in the setup tab; the way I have the setup tab formatted/laid out it makes more sense for them to be in the same column. With your earlier advice to cut down on the amount of import functions I may just do it manually or look for better ways to format the sheet so having the references in the same row doesn't seem out of place. Thanks!
    – cotimms
    Mar 21 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.