using relative references in Indirect

**jrtaylor** · 02-14-2018, 08:10 PM

Is there a simple method of writing an Indirect formula so that I can copy it across to multiple tables, all with same structure? In the example below, I'd like the "E" and "F" to be relative. I looked up the Columns formula but that returns the column number. I've attached a worksheet where this formula appears in rows 75 and 76

Please Login or Register  to view this content.

**ben_hensel** · 02-14-2018, 08:36 PM

Use the ADDRESS function.

In L76:

Please Login or Register  to view this content.

That should copy/paste around as you desire.

**jrtaylor** · 02-14-2018, 09:34 PM

Thanks Ben. Saved me from lots of tedious work!

**MrShorty** · 02-14-2018, 09:40 PM

I don't use INDIRECT() myself. I think the easiest way to get relative referencing in INDIRECT() would be to use R1C1 notation (see optional second argument of INDIRECT() function: https://support.office.com/en-us/art...1-92b6306fa261 ). The advantage to R1C1 referencing in this case is that reference text strings do not change as cells are copied around. A1 references, of course, change.

I apologize for being to lazy to translate all of your complex logic, but a simple example should suffice to illustrate how this works.

=INDIRECT("R[-43]C[4]",FALSE) in A77 (I wanted to be able to compare to your formula in A76) will always refer to the cell 43 rows above and 4 columns to the right -- which should be E34 in this case. If you copy this function across, each copy will refer to the cell 43 rows above and 4 columns to the right of the cell containing the INDIRECT() function, so each successive column returns F34, G34, and so on.

Compare to =INDIRECT("E34",TRUE) in A76. A76 will pull the value from E34, but the reference string does not change as it is copied across, so each copy also refers to E34.

Of course, going from that to the more complex range definition and such that your MAX(...)-MIN(...) functions are doing is more work. I would suggest you start here https://support.office.com/en-us/art...3-c311f47ca173 which has a section that talks about how R1C1 references are put together.