Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 12-11-2009
Member
 
Join Date: Nov 2009
Posts: 36
Copy paste formula automatically in Excel 2007

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?
Reply With Quote
  #2  
Old 12-11-2009
Member
 
Join Date: Nov 2008
Posts: 1,192
Re: Copy paste formula automatically in Excel 2007

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
Reply With Quote
  #3  
Old 12-11-2009
Member
 
Join Date: Nov 2009
Posts: 36
Re: Copy paste formula automatically in Excel 2007

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 
...
Reply With Quote
  #4  
Old 12-11-2009
Member
 
Join Date: May 2008
Posts: 685
Re: Copy paste formula automatically in Excel 2007

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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Copy paste formula automatically in Excel 2007"
Thread Thread Starter Forum Replies Last Post
I cannot do Copy and Paste or Paste Special between Excel Workbooks Acca-OR Windows Software 5 26-04-2012 04:16 AM
Cut/Copy/Paste function is working very slow in Excel 2007 Rounder1 MS Office Support 2 24-02-2012 07:32 PM
How to Copy formula using Vlookup on Excel? Chellappan Windows Software 2 04-01-2012 08:20 PM
Macro onClick copy/paste Excel MACE Software Development 3 09-12-2009 03:58 PM
Copy Text from One Cell to Another in Microsoft Excel without copy & paste Computer_Freak Tips & Tweaks 0 18-03-2009 10:00 PM


All times are GMT +5.5. The time now is 07:28 PM.