Mixed Reference not working

Question

I want to be able to click and drag so that the column changes, but the row doesn't change. However, the function is not working.

='LABOR CALC'!J$110+AA9 This is what I have, and using this I would assume the row stays the same and the column changes, but it is not.

Answer 1

Hi Trisha,

better and easier:

=INDEX('LABOR CALC'!$110:$110,(ROWS($1:1)-1)+10)

Claus

Answer 2

Hi Trisha,

dragging down a formula will change the row number. Dragging to the right or left will change the column letter.

When you will drag down the formula but the column letter should be changed you can try:

=INDIRECT("'LABOR CALC'!"&ADDRESS(110,COLUMN(J1)+ROW(A1)-1,4))+AA99<br><br><br><br>Claus

Answer 3

Hi Trisha,

try:

=INDIRECT(ADDRESS(110,10+ROW(A1)-1,4))+AA99

Claus

Answer 4

"So, in your example you have J$110. Excel will not change the row when it is drug down because that is the part that is preceded by the dollar sign."

Yes, the row is not changing... but how can I have it so that the row does not change, but the column changes. For example,

J110

K110

L110

^ This is the outcome I would like.

Answer 5

Hi Trisha. I am an Excel user like you.

Absolute Value in Excel uses $ signs to tell Excel what parts of a cell reference in a formula we don't want changed when the formula is copied or drug to another cell. The dollar sign must be placed in front of the thing we don't want to change. So, in your example you have J$110. Excel will not change the row when it is drug down because that is the part that is preceded by the dollar sign. If you want the Column to stay the same and the row to change it must be $J110 when you drag the formula across a row. If you want neither to change then you would put dollar signs in front of both as in $J$110.

Keep in mind that, regardless of whether you use a dollar sign or not, Columns will never change unless you drag the formula across a Row, and Rows will never change unless you drag a formula down a Column.

Hope this helps.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M