1

I have five sheets:

  • Overview
  • Invoice
  • 20151017
  • 20151019
  • 20151202

Cell N1 on 'Overview' contains '20151017'. I'd like to use the INDIRECT function to call N1!J3 (so 20151017!J3) to the cell N3 on Overview. This sort of works:

=INDIRECT("'"&N$1&"'"&"!J3")

That is, of course, until I try to extend it down into N4. When it was written &N1& and copied down, N1 would become N2 etc etc. But J3 never changes.

How do I go about addressing that for the next 56 rows down and 2 across?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Alex Masters
  • 45
  • 7

1 Answers1

1

The row number in J3 is text. It will not change. You need to append the ROW function to adjust the string being translated by INDIRECT as you copy down.

=INDIRECT("'"&N$1&"'!J"&ROW(3:3))
  • I was going to do them independently. I'd settle for just down at the moment. Neither of the formulas provided work in this instance ..but I'm not sure what I'm meant to be amending for them to work. – Alex Masters Nov 11 '15 at 07:18
  • I'm going the other way, though: 20151017!J3-J59 needs to appear in Sheet1!N3-59, 20151019!J3-J59 needs to appear in Sheet1!O3-59 etc. – Alex Masters Nov 11 '15 at 07:32