how do you copy an absolute cell reference?

Hi,

I have a workbook that contains data that covers 1600 lines an i have a formula that refers to the column A and then the cell refernce ie. k2=A6.

I need to make the cell an absolute reference by K2=$A$6, however i need then for each cell copied down to say k2=$A$7, K2=$A$8 and so on without having to type them in indvidually, is there any way to do this quickly please.

Take the $ out from in front of the row reference.

So use: =$A6

Trouble is I need the cell reference to stay the same when all the data is sorted so that the cell conatining the formula even when sorted would refer to ID no. in the A column would be referred to say A6 - the formula looks at another cell to ref back for the a6 location - i.e. the information in A6 refers to an ID no. 001 and the formula needs to refer to this

You lost me. Maybe you should use INDEX().

Could you upload a sample file (without private data) showing what you want?

Here is the actual formula

=SUMPRODUCT(-('Stock Movement'!$A$5:$A$7518=Data!$AK$4),-('Stock Movement'!$F$5:$F$7518=Data!$A6),'Stock Movement'!$H$5:$H$7518)

all the parts of this need to absolute cell references including the =Data!$A$6 part but i need to copy this down the workbook and the cell reference needs to change to =Data!$A$7 and so on as each line is copied down - this is the only part of the formula that needs to change as it is being copied down

Hi martin - unfortunatly it dosent for some reason the cell references seem to get mixed up

it needs to be tied to the cell reference that its representing hence the =Data!$A$6 - is there any way to make it flexible so that if it is sorted then it automatically updates and refers to the cell reference on the line it is sorted to - if that makes sense?

i cant put the spreadsheet on here but i can put 2 screenshots on via a word document if that helps it would show the probelm that is occuring

is there any way to make it flexible so that if it is sorted then it automatically updates and refers to the cell reference on the line it is sorted to

Are you looking for =INDEX(Data!$A:$A,ROW()) maybe???

BTW - Your SUMPRODUCT() formula has single "-"'s but you need doubles "--" or just use the multiplier (*) between components.

how would I incorporate the =INDEX(Data!$A:$A,ROW()) into my formula the ),-('Stock Movement'!$F$5:$F$7518=Data!$A6) section has to look at a stock movement sheet between rows F5 and F7518 to find the corresponding ID No. listed in A6 ? i.e. if the Id no. in A6 is 001 then tghe formula looks at the stock movement sheet and tallies all the instances of id no. 001 and its corresponding item issues hopw this makes it clearer to understand

hope this makes it clearer to understand

Without a sample file, not for me. Hopefully for martindwilson or others.

ok will try and post a file