Copying formulas but keeping parts constant

[rohitbisani]

Hello Everyone, This is Rohit

I have the following formula in the excel sheet....
"=C5-VLOOKUP(A5,Sheet1!A5:E8,3,FALSE)"

What I want is , when I copy the formula in subsequent rows, the cell referencecs change accordingly..
like in this one, c5 changes to c6, a5 changes to a6, A5:E8 changes to A6:E9...


I want all changes to occur except A5:E8 - > A6:E9 .. this change mustnt occur,,
It should remain the same table A5:E8
HOw can i do that?
Please help

 

[VoG]

Try

=C5-VLOOKUP(A5,Sheet1!$A$5:$E$8,3,FALSE)

the $s make the range absolute.

 

[mvptomlinson]

To create static cell references in Excel, use $ in front of the column letter or row number (or both to keep them completely static).

In your example, and in most VLOOKUP cases, try:

=C5-VLOOKUP(A5,Sheet1!$A$5:$E$8,3,0)

 

[rohitbisani]

Hey I got the answer...... Please Ignore... sorry

 

[rohitbisani]

Thankx peter and mvptomlinson