Formula keeps changing

[Pete Primmer]

I have a formula to copy and it keeps changing.

=VLOOKUP(RIGHT(A6,2), Values!$A1:$B104,2,FALSE)

When I copy it down the page it brings back A6 updating
correctly but the $A1:$B104 changes and short of changing
999 entries I cant get it to paste special at all.

I have been pulling my hair out! Can anyone solve this
for me?

Pete

 

[Gord Dibben]

Pete

Add some more $ signs in first formula then re-copy down the column.

=VLOOKUP(RIGHT(A6,2), Values!$A$1:$B$104,2,FALSE)

Another method is the give your table a defined name like MyList

Select A1:B104 and Insert>Name>Define

Use the name instead of cell references in your formula.

Gord Dibben Excel MVP