Getting autofill to stop use a fixed cell

[KeiranMac]

Hi,

I'm using autofill and struggling to get it to both carry out it's useful autofill function, i.e. adjusting the cells referenced each time I drag it along, and to keep referring to one cell for part of the formula.

e.g. If in B2, B3, B4, I want a code that says =SUM(C2*G5), and then I drag it to B3, B4, B5, etc etc, it will adjust it to =SUM(C3*G6) .... but is there a way of getting the code to both autofill for C2, C3, C4 etc, yet remain fixed on the G5 cell?

The attachment explains better. I want to be able to change the value in G5 to reflect a change in circumstances, i.e. costs, yet still be able to autofill the formula down. As you'll see, the price for apples remains five but the autofill makes a mess out of this as it will keep changing both the cells it refers to, instead of just one.

There's probably a way of telling Excel you want part of the code to remain the same without applying the autofill, whilst keeping the usefulness of autofill for other parts... it would be handy to know how!

thanks,

Km,

[DonkeyOte]

You probably want to read up on Mixed References etc in XL Help.

It seems to me that you want:

B2: =$C2*VLOOKUP($D2,$F$4:$G$6,2,0)
copied down

[KeiranMac]

Thanks muchly, I think the dollar signs are the key to this by the looks of it, but I'll take a look at mixed references too.

Cheers!