Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
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  
+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

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.