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

microsoft-excel-2010worksheet-function

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.

enter image description here enter image description here

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?

Best Answer

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 combined with ADDRESS

=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...