How to drag formula and keep column reference but change row number

[lee2121]

Hello.

I have tried to find this out on the internet but nothing i have tried seems to work and this seems like a very easy question.

=IF(B2='Data Readings'!$A23,'Data Readings'!$AL23,0)

I have the formula above in cell (B2) and when i drag it to cell (C2) i want the formula to only change the two cell reference A23 changes to A24 and AL23 changes AL24.

Any help will be appreciated.

 

[RoryA]

this seems like a very easy question

It would be if you were dragging down, not across. When you drag across, Excel expects you to want to increment the column, not the row.

I don't understand your question though - if that formula were in cell B2, you would be creating a circular reference as the formula tests the value of B2.

 

[lee2121]

Sorry my fault, B2 changes to C2 when i drag which is working fine, so when i drag i want the new formula to be like example below.

=IF(C2='Data Readings'!$A24,'Data Readings'!$AL24,0)

 

[RoryA]

Which version of Office do you have? I suspect there are simpler formulas you could use to check a lot of cells with one formula.

You have to appreciate that with the formula you have, Excel would have no possible way of knowing that you don't want the row number to change in the first argument, but you do want it to change in the other two.

 

[lee2121]

I'm using office 365.

My formulas are in cells B5 to R5 i have 20 different sheets like the one below but they all fetch data from one sheet.

The data comes from this sheet.

 

[RoryA]

With 365, you should be able to just enter this in the first cell:

=IF(B2:C2=TRANSPOSE('Data Readings'!A23:A24),TRANSPOSE('Data Readings'!AL23:AL24),0)

adjusting the ranges to cover the cells you need.

 

[lee2121]

With 365, you should be able to just enter this in the first cell:

=IF(B2:C2=TRANSPOSE('Data Readings'!A23:A24),TRANSPOSE('Data Readings'!AL23:AL24),0)

adjusting the ranges to cover the cells you need.

When i use this formula it shows me #spill error.

 

[RoryA]

You only enter it into the first output cell - all the others in the row need to be empty as it will spill results into them automatically.

 

[lee2121]

You only enter it into the first output cell - all the others in the row need to be empty as it will spill results into them automatically.

Oh my, that's really smart, thank you works perfect. I need to look into this as looks like a really good new feature

 

[RoryA]

looks like a really good new feature

It is a real game changer.