# locking a formula to a cell

#### jondallimore

##### Board Regular
Hello,

This is probably something really obvious but Ive been bashing my head against a brick wall for a while and its starting to get squishy.

I am referring to a cell, A1, in formulae in a set of cells that go from A1 to B4.

When I copy these cells, the references to A1 become references to A7.

I would like these refereces to refer to A2 instead - How do I do that, and can it be done with little sleep and a squishy head?

Thanks

Also... I am using a VLOOKUP formula which I always want to refer to the top right cell in the range of 8 cells I am using, even when the formula is copied to another range of cells.... Any way to do that?

Thanks again.

Last edited:

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
jondallimore,

You need to be using an ABSOLUTE reference to the cells that need to be 'fixed' within your formulas.
You do this by using the \$

A1 is wholly relative and when copied Excel will relatively update the address.

\$A1 if copied elsewhere will not change the column A but will relatively change the row. A\$1 will do the opposite.

\$A\$1 will ensure that cell A1 does not change at all.

If you have a range highlighted within in a formula and hit F4 key it will toggle through the various combinations from wholly relative to wholly absolute.

Eg Drag =B1*A1 to the next cell right and it becomes =C1*B1

Drag = B1*\$A\$1 to the next cell right and it becomes =C1*\$A\$1 thus preserving the reference to cell A1

Probably not the best description but hope it helps.

Last edited:
yep, i get the \$ function. However, if I have \$A1 and I copy the formula in B1 to cell B7, the formula then refers to A7. How can I make it refer to A2 without having to edit it manually each time?

Sorry for the delay, access to this site is desperately slow for me tonight.

Can you perhaps post an example of what you want?

Is it reference \$A1 in B1, B2 ,B3 ,B4

then a jump tp B7,B8,B9,B10 that need to reference \$A2 and so on or what?

The only way you will do this is if there is a relationship between the increment of A and the formula blocks in B.

yep, thats it. B1 referrs to A1, then B7 needs to refer to A2, B14 to A3 and so on. Ive decided the easiest way to do it is just to stop procrastinating and do it manually - unless you come up with a stupendous solution in the next 30 mins or so!

thanks anyhow.

Try this.....

Excel 2007
AB
12222
22322
32422
42522
5
6
723
823
923
1023
11
12
13
1424
1524
1624
1724
Sheet1
Cell Formulas
RangeFormula
B1=OFFSET(\$A\$1,ROUNDDOWN(ROW(A1)/7,0),0)

Hope that helps.

thanks. Done it manually now, but will bear that in mind for next time.

Replies
11
Views
259
Replies
3
Views
136
Replies
0
Views
228
Replies
4
Views
73
Replies
5
Views
71

1,196,514
Messages
6,015,648
Members
441,913
Latest member
Lhayden_69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back