How can I copy formulas without changing some of the cell numbers?

**MathiasH** · 10-08-2011, 08:09 AM

If I have a formula like this: Vlookup ("A3",A5-A10;2;0), and I want to copy this formula to another row the formula will often change to something like: vlookup("A8",A10-A15;2;0).

I want the formula to be (A8,A5:A10;2;0).

I know I can use the "hold cell" function by using the $ button. However this means that the A5-A10 wont change through the different columns. I want to be able to have functions like this: Vlookup("A3",b5:b10,2;09....c5:c10....d5:d10... without doing it manually.

Can anyone help solving my problem?

**xov0ct** · 10-08-2011, 08:29 AM

Do you a sample file?

**MathiasH** · 10-08-2011, 08:42 AM

Originally Posted by xov0ct

Do you a sample file?

Sorry but I dont understand your question.

**xov0ct** · 10-08-2011, 08:44 AM

Sorry, what I mean is, "Do you have a sample file?".

**MathiasH** · 10-08-2011, 09:01 AM

I dont understand what you mean by "sample file".
I have an excel spreadsheet with a lots of numbers that I want to value using some formulas, if that answers your questions.

**GeneralDisarray** · 10-08-2011, 09:06 AM

Ok, you can dollar sign only the number (not the letter) I think this i what you are after:

$A$10 - Freezes column / row on paste
$A10 - Freezes column only
A$10 - Freezes row only <---- i think you want this one.

----

Example address range, A5:A10

IF you replace with this: A$5:A:10 and then copy / paste the formula one column to the right, the address range will become B$5:B$10.

By "dollar-signing" just the row numbers you will allow the formula to adjust COLUMN LETTERS when a column shift is appropriate...

-----
While we're on the subject you can Freeze the Letters (columns) and not the rows (numbers) also by doing this: $A5:$A10....that will change the numbers based on which row you paste to....

**MathiasH** · 10-08-2011, 09:27 AM

Originally Posted by GeneralDisarray

Ok, you can dollar sign only the number (not the letter) I think this i what you are after:

$A$10 - Freezes column / row on paste
$A10 - Freezes column only
A$10 - Freezes row only <---- i think you want this one.

----

Example address range, A5:A10

IF you replace with this: A$5:A:10 and then copy / paste the formula one column to the right, the address range will become B$5:B$10.

By "dollar-signing" just the row numbers you will allow the formula to adjust COLUMN LETTERS when a column shift is appropriate...

-----
While we're on the subject you can Freeze the Letters (columns) and not the rows (numbers) also by doing this: $A5:$A10....that will change the numbers based on which row you paste to....

Yes thank you. That was what I was looking for.