Help With Copying Formula

[rockyw]

I have a workbook with a formula in Q that is =Data!G8. I want to copy this formula acrost the work book to R thru Y, each changing to Data!G9, Data!G10 ect. I then have the same type of formula for other rows. If I drag the formula acrost the columns the formula changes to =Data!H8, =Data!I8, I don't want the column changing, just the row. How can I get excel to copy these formulas fast and easy? Thanks

 

[Joe4]

Use this formula:

=INDIRECT("Data!G" & COLUMN()-9)

 

[rockyw]

I put that in column Q and dragged it to the right, it just copyed the same formula. Can't excel copy to the right and just increase the rows, not the columns? Thanks

 

[Joe4]

It DOES increase the rows!

Just make sure your have your cells formatted to General and not Text so it treats asa a formula and not text.

Take a look at the INDIRECT function in Excel help to understand how it works. The COLUMN() function returns the current column the formula is found in. So as you copy it across a column, it keeps incrementing. So we are setting the row portion of the INDIRECT function to increase as the column increases, which is what you want.

The formula won't actually say =Data!G8, =Data!G9,..., but that is what it is returning. Test it and check the values and you will see that is what it is doing.

 

[rockyw]

What I need it when copying to the right acrost columns only the row will change. Make sence? I tried =Data!$G7 to hold the column but the row still will not increase. Thanks

 

[grizle]

Whilst the formula is not the same, it returns the same result by indirect addressing: Q is column 17 so

=INDIRECT("Data!G" & COLUMN()-9)
=INDIRECT("Data!G" & 17-9)
=Data!G8

As you copy right COLUMN increments which increments the row reference

 

[Joe4]

Do you read or try what I suggested?

If you place the formula I gave you in column Q, it will return the value from Data!G8.

If you copy that formula out to column Y, this is what it will return in each cell:

column R: value from Data!G9
column S: value from Data!G10
column T: value from Data!G11
column U: value from Data!G12
column V: value from Data!G13
column W: value from Data!G14
column X: value from Data!G15
column Y: value from Data!G16

That is what you asked for, right?

I have a workbook with a formula in Q that is =Data!G8. I want to copy this formula acrost the work book to R thru Y, each changing to Data!G9, Data!G10 ect.

You cannot get the formula to work the way you do, increasing rows while copying across columns without using the INDIRECT function or VBA.

 

[rockyw]

Sorry Joe I was reading and trying. While I was reading and trying you were adding more info. While I was was not understanding and asking a new question you were adding a new answer. I see now what you are saying. And YES, it works as you say. I do not understand that the formula does not change, but the cells it's looking at do. Weird but it works. Thank You.

 

[Joe4]

They key to the formula incrementing is the COLUMN() function. It returns whatever column it is found in.

Try this little test and it should make more sense.
In column R, enter this formula:
=COLUMN()
Now copy that formula out to Column Y and see what it returns in each cell.

Basically, what we are doing is using math on the current column number to get to the row number we want. Since column R returns 17 (it is the 17th column), we subtract 9 to get to 8, the first row we want.

So column S returns 18. Subtracting 9 gives us 9 (to return row 9).
Etc, etc.

The INDIRECT function is just what you use when you are building a cell reference from a formula instead of typing it in directly. Excel's built-in help has some explanations and examples.

 

[rockyw]

It's making sense now, I will play around with this today. thank you for the help, have a good day.