INDIRECT and Relative Referencing?

tinfanide · #1 03-03-2016, 03:12 AM

Code:

Absolute referencing:
=INDIRECT("'"&B$1&"'!$N"&ROW()+1)

VS

Relative referencing:
=INDIRECT("'"&B$1&"'!$"&N&ROW()+1)

The relative one returns an error: #NAME
Although the absolute one is correct, I would like to have the column ("N" in this case) relative. This means when I insert a new column (then "N" should be "O"), the formula updates it automatically. How could I achieve that?

Any help is appreciated.

xor · #2 03-03-2016, 06:28 AM

Maybe like this:

tinfanide · #3 03-03-2016, 06:48 PM

Quote:

Originally Posted by xor

Maybe like this:

Yes. It turns into relative referencing. Thanks.
But technicially speaking, I'm wondering if the blue highlighted one (below) could be all in one formula (so that there is only one cell).

Of course, the red one (my testing) returns an error (i.e. #REF!).

Code:

=INDIRECT("'"&B$1&"'!"&ADDRESS(ROW(),???))

=INDIRECT("'"&B$1&"'!"&ADDRESS(ROW()+1,COLUMN(INDIRECT("'"&B$1&"'!"&N$1)))

xor · #4 03-03-2016, 10:49 PM

You wrote:

But technicially speaking, I'm wondering if the blue highlighted one (below) could be all in one formula (so that there is only one cell).
---------
One should always be cautious arguing that something is impossible in Excel, but here I think such a claim is justified. At least I don't know of any way to get what you want.

And by the way, my formula does not at all work in general, only for the sheet presently specified in B1. If for example you insert Sheet3 and change B1 to Sheet3 you will need to have a similar formula as the one in H1 in for example I1 referring to Sheet3 and change the formula to: =INDIRECT("'"&B$1&"'!"&ADDRESS(ROW()+1;I1))

(It may be possible to automate the change of the last argument, H1, I1,.... as you change B1).

tinfanide · #5 03-07-2016, 05:46 AM

Yes, I agree, if there's any workaround or a trick to get over it.

xor · #6 03-07-2016, 06:37 AM

@tinfanide

Here you have it all in one formula.

xor · #7 03-07-2016, 06:54 AM

Please be aware that I forgot to change semicolon to comma in my comment in the attached file Sheet1!N14. (I use semicolon as argument separator).

tinfanide · #8 03-15-2016, 06:23 AM

Quote:

Originally Posted by xor

Please be aware that I forgot to change semicolon to comma in my comment in the attached file Sheet1!N14. (I use semicolon as argument separator).

Thanks for the workaround. It's perfect!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Indirect with hlookup	jamesjts	Excel	3	01-05-2016 09:13 AM
Indirect AND OR Formula Help	del_piero_3	Excel	3	09-18-2015 02:58 PM
Indirect Reference?	tinfanide	Excel	2	08-28-2015 05:11 PM
Confusion on INDIRECT function	thatgirlaudrey	Excel	4	06-06-2014 09:23 PM
Indirect	zleyphox	Excel	1	02-11-2010 09:57 AM