# Copy formula

#### deanl33069

##### Board Regular
I have a simple formula starting in column AD6-AQ6
in AD6 i have =aa06 AE6 =aa07 AF6 =aa08 ect
it stops in cell AQ6 then drops down to AD7 over to AQ7 then down to AD8-AQ8
when i copy cell AD6 and choose the rest of the cells (ae6-aq320) the formula changes from AA to AB
how can i keep it all AA and not change to AB or AC ect

Maybe change
=AA6
to
=\$AA6

?

#### James006

##### Well-known Member
Hi,

In cell AD6 you could have following formula:

Code:
``=OFFSET(\$AA\$6,COLUMN()-30,0)``
which can then be copied from AD6 till AQ6

Hope this will help

#### deanl33069

##### Board Regular
ty but didnt work

#### Gerald Higgins

##### Well-known Member
Which one didn't work, you've been offered two solutions ?

And in what way exactly did it not work ?

#### deanl33069

##### Board Regular
fIRST LINE IS ALL \$AA6
second is \$AA7 ect

#### deanl33069

##### Board Regular
did not copy right filled each line the same formula

#### Joe4

I think Gerald missed the first part of your question, and didn't notice that you are wanting to copy the formula across the row, but you actually want the column piece to increment. His solution shows you how to lock down the column reference all right, but won't increment the row part.

James formula will do exactly what you want for the first row.

However, I am not sure what you want the formula to look like in the second row. What formula do you expect to see in AD7?

Last edited:

#### Joe4

If the formula in AD7 should be pulling from AA20, then try this modication to James's formula:
Code:
``=OFFSET(\$AA\$6,(COLUMN()-30)[COLOR=#ff0000]+(ROW()-6)*14[/COLOR],0)``

#### Gerald Higgins

##### Well-known Member
Joe4, you're right, I missed the row increment part, apologies to all !

1,078,492
Messages
5,340,686
Members
399,389
Latest member
JayNExcel

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...