locking a formula to a cell

[jondallimore]

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.

 

[Snakehips]

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.

 

[jondallimore]

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?

 

[Snakehips]

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.

 

[jondallimore]

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.

 

[Snakehips]

Try this.....

Excel 2007
	A	B
1	22	22
2	23	22
3	24	22
4	25	22
5
6
7		23
8		23
9		23
10		23
11
12
13
14		24
15		24
16		24
17		24
Sheet1

Cell Formulas
Range	Formula
B1	=OFFSET($A$1,ROUNDDOWN(ROW(A1)/7,0),0)

Hope that helps.

 

[jondallimore]

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