Copy paste formula automatically in Excel 2007

**Macadrian** · 12-11-2009

When you type a formula

Code:

= Sheet1! A1

if you drag the cell down, it becomes

Code:

= Sheet1! A1 
= Sheet1! A2 
= Sheet1! A3 ...

if you block the cell and you do the same thing, it becomes

Code:

= Sheet1! $ A $ 1 
= Sheet1! $ A $ 1 
= Sheet1! $ A $ 1 ...

but I would like the cell is blocked and the leaf changes, type

Code:

= Sheet1! $ A $ 1 
= Sheet2! $ A $ 1 
= Sheet3! $ A $ 1 ...

drawing the formula down the column
how? How to have custom copy and paste of formula in Excel 2007?

**void** · 12-11-2009

Try this on line 1 of another sheet:

Code:

=INDIRECT("Sheet"&ROW()&"!A1")

formula to copy down the names of leaves increment.

If the formula begins in ROW 5 (for example), replace the formula ROW() with ROW() -4

**Macadrian** · 12-11-2009

I thought there was a trick even easier but it works and it will save me much time.

I wanted to ask one more thing on this subject with always the starting formula

Code:

= Sheet1! A1

drawing the formula down I wanted to have

Code:

= Sheet1! A1 
= Sheet1! B1 
= Sheet1! C1 
= Sheet2! A1 
= Sheet2! B1 
= Sheet2! C1 
= Sheet3! A1 
...

**fellah** · 12-11-2009

I'm not sure it is the simplest, but it works

Code:

=INDIRECT ( "Sheet" &ROUNDUP ( ROW ( ) / 3 ; 0 ) & "!" &CHAR ( IF ( MOD ( ROW ( ) ; 3 ) = 0 ; 3 ;MOD ( ROW ( ) ; 3 ) ) +64 ) & "1" )

To better visualize what you're doing exactly, I suggest you remove the indirect and not put that at the very end. In other words, indirect ("A1") will give the same result as do = A1

So the above formula can be returned as

Code:

=ROUNDUP (number; no. of decimal)

So for example"
ROUNDUP (5.51, 1) will give 5.6,
ROUNDUP (5.01, 0) will give 6

Code:

  = ROW ()

Gives No line on what it is

Code:

  ROUNDUP (ROW () / 3; 0)

Gives a whole number that changes every 3 lines (if you met / 5 it will change every 5 lines for example).

The goal is to increase the page number, but not every time. History can have the 3 cells of your page 1 and then 3 cells of your page 2 ...

What I'm trying to make him do it then

"If it is a line that is 3X +1 (1, 4, 7, 10 ,...) then shows the letter A. If a line is 3x +2 (2, 5, 8, 11 ,...), then shows the letter B. And if a line is 3x 3 (3, 6, 9, 12 ,...), then shows the letter C "

Code:

  = CHAR(number)

Allows from a number of the letter found with the code.
65 = A
66 = B
67 = C
...

I used because it skips to the next letter. So with that, I spend a
"If it is a line that is 3X +1 (1, 4, 7, 10 ,...) then found from a line No. 65. If a line is 3x +2 (2 , 5, 8, 11 ,...), then found from a line No. 66. And if a line is 3x 3 (3, 6, 9, 12 ,...), then was found from # line 67 "

Code:

  = Mod (number, divisor)

So the integer remainder of a division
= mod (10, 3) equals 1
= mod (15, 4) is 3

And so if you do mod (row (), 3), it'll give 0 if it is a 3x 3 line, 1 if a line 3X +1 and 1 if it is a line 3X 2

It becomes much, but the 0 bothers me, I'd love a 3 instead (so I could make my 64 + results, which would give me, along line 65, 66 or 67)
So I put a little if (result = 0, 3, result)

I gather it all, I do not forget to add a "1" to finish

If you want to adjust the formula, for example because you have more than 3 letters a post, just change every 3 to something else (5 if you want, from A to E for example)

And it is a formula which works only until the letter Z, if there are more column than that, it will take another