How to lock in specific cells in a formula going down rows

Solved
JoshL - Jun 24, 2022 at 12:44 AM
 JoshL - Jun 27, 2022 at 09:42 PM
Hello,
I am having an issue with easily repeating a formula I've made. the formula is

=IF(AP2=Sheet2!K3,Sheet2!L3,IF(AP2=Sheet2!K4,Sheet2!L4,IF(AP2=Sheet2!K5,Sheet2!L5,IF(AP2=Sheet2!K6,Sheet2!L6,IF(AP2=Sheet2!K7,Sheet2!L7,IF(AP2=Sheet2!K8,Sheet2!L8,IF(AP2=Sheet2!K9,Sheet2!L9,)))))))

I want to lock in the 'K' and 'L' values so as the 'AP' values are increasing going down the rows of the spreadsheet, the 'K' and 'L' values remain the same.

Every time I click and drag to try and repeat the formula going down the spreadsheet excel automatically wants to change all the values, for example like this

=IF(AP3=Sheet2!K4,Sheet2!L4,IF(AP3=Sheet2!K5,Sheet2!L5,IF(AP3=Sheet2!K6,Sheet2!L6,IF(AP3=Sheet2!K7,Sheet2!L7,IF(AP3=Sheet2!K8,Sheet2!L8,IF(AP3=Sheet2!K9,Sheet2!L9,IF(AP3=Sheet2!K10,Sheet2!L10,)))))))

I want the 'AP' values to increase down the rows of the spreadsheet but I do want the 'K' and 'L' values to increase. Any advice on how I could accomplish this? Than you for your help.




System Configuration: Windows / Edge 101.0.1210.39

1 reply

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Jun 27, 2022 at 12:01 PM
Hi JoshL,

What you are looking for is the dollar symbol.

When you place it in front of a column letter, dragging that cell horizontally won't change the column letter.
Placing it in front of a row number, dragging that cell vertically won't change the row number.
You can also place it in front of both column letter and row number to lock the cell reference completely.

When placing your cursor in the middle of a cell reference (or multiple / all) of your formula, hitting F4 will cycle through all the variations.

In short, your formula would look like this:
=IF(AP2=Sheet2!K$3,Sheet2!L$3,IF(AP2=Sheet2!K$4,Sheet2!L$4,IF(AP2=Sheet2!K$5,Sheet2!L$5,IF(AP2=Sheet2!K$6,Sheet2!L$6,IF(AP2=Sheet2!K$7,Sheet2!L$7,IF(AP2=Sheet2!K$8,Sheet2!L$8,IF(AP2=Sheet2!K$9,Sheet2!L$9,)))))))

Best regards,
Trowa

1
Hi Trowa,

Thank you so much! That worked perfectly, really appreciate your help. Have a good day.

Kind regards,
Josh
0