1

I've been trying to figure this out for a while.

I need to find the content of a specific cell relative to my current cell. The relativity isn't the typical counting relationship but rather an equational relationship (if there's a word for the form of relativity, I'm curious to know what it is).

Anyhoo, this will return the column/row I'd need for any cell:

=ADDRESS((ROW()+8-MOD(ROW(),4))/4,2)

So I've got that information. Now I need to get the content of that cell. I've tried the CELL function, I've tried using this formula: (Let it be noted, I'm getting the cell from a different sheet).

='Form Responses 1'!ADDRESS((ROW()+8-MOD(ROW(),4))/4,2)

So far, I've gotten nowhere.

Any ideas?

user3627232
  • 11
  • 3
  • possible duplicate of [Get content of a cell given the row and column numbers](http://stackoverflow.com/questions/4813888/get-content-of-a-cell-given-the-row-and-column-numbers) – LondonRob Jul 08 '15 at 17:20

2 Answers2

0

"=Address" Gets you a reference as text. If the cell you are working on is Sheet1 A1, try something like:

=!Address!B2/!Address!B4

The first "!" will get you the "Address sheet" and the second will maintain the relationship between cells. Just sub in the cells you need for the first set then copy the formula down the column.

msiudut
  • 251
  • 1
  • 20
0

The function you're looking for INDIRECT.

INDIRECT is a volatile function so using it a lot can cause performance problems. A volatile function is one that causes formulas to be recalculated every time any cell changes.

Another function you might find useful is OFFSET (also a volatile function).

=INDIRECT("'Form Responses 1'!"&ADDRESS((ROW()+8-MOD(ROW(),4))/4,2))
Tmdean
  • 9,108
  • 43
  • 51