Dynamic text reference in Indirect formula

[kaushik03]

Hi All,


Wanted to know a trick from you...


Say, from A2 to E4, I have the following data


10>>>>> 15>>>>> 20>>>>> 25>>>>> 30

50>>>>> 80>>>>> 56>>>>> 52>>>>> 75

20>>>>> 20>>>>> 12>>>>> 30>>>>> 28


Now, at G2, I write following INDIRECT formula to fetch the data:


=INDIRECT("A"&ROW(A2))


My question is, how can I make "A" portion dynamic so that when I drag the formula across, "A" turns to B, C (so on and so forth) and hence other columns' data gets updated.


Kaushik

 

[Luke M]

We can switch to using R1C1 style references by stating FALSE for the 2nd argument in INDIRECT, like so:

=INDIRECT("R"&ROW(A2)&"C"&COLUMN(A2),FALSE)

 

[anupamtiwari05]

Hi Kaushik,


Luke's formula looks more dynamic however you can also try the below formula.


=INDIRECT(CHAR(62+ROW(A$2)+COLUMN(A2))&ROW(A2))


Thanks & Regards,

Anupam Tiwari

 

[kaushik03]

@Luke: Excellent....as elegant as always...


@Anupam: I was also trying to build the formula with CHAR function but was not able to come up with the right one...Thank you very much for sharing this..


Applied your concept here:


http://chandoo.org/forums/topic/in-vlookup-match-how-to-update-contents-of-next-row-of-same-lookup-value


Best regards,

Kaushik

 

[nazmul_muneer]

Kaushik,

You can try this formula. Type in G2

=INDIRECT(ADDRESS(ROW(A2),COLUMN(A2)))


Regards,


Muneer

 

[kaushik03]

Thanks Muneer...

 

[nazmul_muneer]

Kaushik,

Happy to share with you.


Muneer