# Can cell addresses (vs. cell contents) be used as operands in Excel formulae?

Consider the following scenario illustrating the data I have vs. the data I want. Very simply, I have a single column that represents Field 1, Contents of Field 1,...etc. and is more appropriately formatted as pairs of rows across columns.

I've been experimenting with the `TRANSPOSE` function, but that one's new to me and I'm not sure it's the right direction. At first glance, it doesn't seem to accomodate my needs. But I could be wrong...

While it would be relatively simple to set up an 'every other row' formula, I also have to account for situations where I may have a `foo` repeating with a single `bar` or vice versa, and that's where I'm stuck. What I need is a "next unused cell" function, or something that can add X rows or Y columns to a particular cell.

I can envision a formula that can manipulate the cell label and solve my problem: `=(\$A1+2rows)` or some kind of `=\$(A+3)\$(4+2)` that would return cell `D6`.

Is something like that possible?

-
Check out the `INDEX` function. This will allow you to flexibly return a value. Alternatively, `INDIRECT` and `OFFSET` will also be useful! – Peter Albert Mar 11 '13 at 13:36
+1 for `OFFSET`; it's exactly what I was looking for. I don't suppose there's a `PULLESPRESSO(numberOfShots,lengthOfPull)` function somewhere...I'm stating to think that Excel can do EVERYTHING! – dwwilson66 Mar 11 '13 at 13:49
LOL! Check out the INDEX function though - it is equivalent to OFFSET in many ways, but more efficient - see my answer below. If it solved your problem, please mark it as the answer! Thx – Peter Albert Mar 11 '13 at 14:14
If you just wanted to flip from rows/columns you could use the steps here: office.microsoft.com/en-us/excel-help/…. Give that you want to move some cells one way and others another Peter's examples work better. – Brad Patton Mar 11 '13 at 14:22

There are three functions you can use for your scenario:

OFFSET

Using `=OFFSET(\$A\$1,NumberOfRows,NumberOfCols)`, you can bascially shift the reference to cell A1 in any direction.
Pros: Easy to use; can also be used to create a range of multiple cells (e.g. in dynamic names), using fourth&fifth parameter
Cons: Volatile, i.e. Excel will recalc formula every time

`=INDIRECT(ADDRESS(RowNum,ColNum,,,Sheetname))` will allow you to access any cell in Sheetname. (If you leave out sheetname, it'll work use the current worksheet.
Pro: Can handle multiple workhsheets
Con: Volalite, can't handle other workbooks unless opened

INDEX

`=INDEX(\$A:\$Z,RowNum,ColNum)`
Pro: Non-Volatile, i.e. will not slow down recalcs in large models; very versatile (e.g. in combination with MATCH)
Cons: Array needs to be specified up front

Personally, I always try to use `INDEX`, only on occasions using `INDIRECT`(basically when the sheet name needs to be dynamic) - and almost never `OFFSET`...

-