Reference Row and Column using function R1C1

[djamieson18]

Hi Folks,

I was wondering how I could reference a row and column using a function for each.

e.g.

If I want the cell to equal R1C1 then I enter =R1C1 into the forumla ... obviously

but if I want this to be dynamic then how do I reference R(a function)C(another function)

e.g.

=R(sum(colA))C(sum(colb))

thanks,

Dave

 

[Firefly2012]

Hi Dave

You need to use INDIRECT:

=INDIRECT("R" & SUM(A:A) & "C" & SUM(B:B),FALSE)

I am assuming that you don't have R1C1 applied in Excel? If you do, then the SUM() would need to contain R1C1 style references. The second argument in INDIRECT above tells the function it is receiving an R1C1 style string.