Mixed reference doesn't work - Excel 2010

[marinapaz]

Hi, I am having the same trouble on both my computers. Both are Windows 8, but one has Excel 2010 and the other, 2013.

Absolute referencing, such as $A$1 works fine. But when I try to work with mixed referencing, such as A$1, or $A1, it just doesn't work, and I have no idea why.

Basically, what I would like to do, is to be able to reference a column in a row, or the other way around. I used to just do the following, use $A1 and drag it to the right, and it used to work:

1		=$A1
2
3
4
5

<tbody>
</tbody>

Now, if I do this, all I get is this:

1		1	1	1	1	1
2
3
4
5

<tbody>
</tbody>

I've been using TRANSPOSE, but for using the cells in formulas, I would need to used mixed reference. Does anyone know how to solve this?

 

[Jonmo1]

Welcome to the board.

That is the designed behavior of a formula.
If you put
=$A1 and Drag 'to the right', it will STAY $A1

Perhaps you want
=A$1 and drag right, now it will change to B$1 C$1 etc as it's dragged to the right...

$A$1
The Red $ locks the COLUMN reference
The BLUE $ locks the ROW reference.

 

[bbott]

When using relative references, dragging across will only increment columns, not rows. Maybe something like this will work for you in C1:

=INDEX($A$1:$A$5,COLUMN(A1))

This can be dragged across but will increment rows.

 

[marinapaz]

Thank you so much! This INDEX solution is so simple!

 

[bbott]

Thank you so much! This INDEX solution is so simple!

You're welcome.