Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I can explicitly reference cell G42 or range A42:G42.
But what if I need something dynamic?
For example: I want to write an expression in cell G200, which would calculate the average of cells A42:G42, without explicitly writing A42:G42, but by taking the value of cell G201, which is 42, finding "G" by seeing in which column the current cell is and finding "A" by going seven columns back from column G.
Is this possible?
This can be done by using indirect in R1C1 notation. The following formula matches your situation:
=indirect("R" & G201 & "C" & column()-6 & ":R" & G201 & "C" & column(), False)
The range is described by a string formed by concatenating
R<row number>C<column number>:R<row number>C<column number>
The row numbers are taken from G201, the column numbers are computed with the column() function that returns the column number of current cell. (By the way, A is 6 places to the left of G, not seven)