Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Consider the following excel worksheet:

  | ... | H      | I    | J
1 | ... | obsDay | obs3 | obs4  ...etc
2 | ... | 3      |      |     
3 | ... | 4      |      |     
4 | ... | 8      |      |     

In A2 to A148 I have 147 obsDay values. I now need to make a separate column for each value. The values in cell B1 and C1' are respcetively there because of the formulas="obs"&A2and="obs"&A3`

My question is: I cannot drag the formula to all other 145 columns. I I would drag the formula of B1 to the right, C1 gets value obs, with underlying formula ="obs"&B2. I know I can fix column a like his: ="obs"&$A2, but if I thne drag it to the right, C2 gets value 'obs3' as well (with formula ="obs"&$A2 underlaying), while I need it to be ="obs"&$A3, then ="obs"&$A4, ="obs"&$A5, etc..

What am I doing wrong?

share|improve this question
up vote 1 down vote accepted

Have you looked into the INDIRECT() formula? Using it, you could put something like


in cell B1 and copy it to the right. There's probably a more efficient way of doing it, but that will work.

share|improve this answer
Thanks your your reply. It works, as described in the opening post. In reality however, the data is not in columns A to C, but in H to 'J'. I tried editing your formula: ="obs"&INDIRECT("H"&COLUMN(H1)+1) but that doesn't work. IT just returns obs...what could be wrong? I have no clue, since I'm pointing to the right columns and rows, right? – Pr0no Aug 20 '12 at 15:01
No problem, I'm glad it worked for you. The COLUMNS(A1) is just a cheap way of getting a number that starts at 1 and increases as you copy it to the right. Leave the COLUMN() formula as it was in my original answer and swap the in the "H" like you've selected to give you something like ="obs"&INDIRECT("H"&COLUMN(A1)+1) – bobmagoo Aug 20 '12 at 15:05

to translate from data reading down to going across, I use offset.


This will take the current column, and using offset, translate that into the number of rows to go down from the anchor cell.
Add or subtract from the COLUMN() number if needed

share|improve this answer

Your Answer


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.