Copying Formula

N

Nad

Hi Every One,
How can I copy a formula after(Leaving) one cell?
Because by default when i double click the fill handle it copies the formula
in continuous cell. But i want to copy the formula leaving one cell.

Best Regards,
 
M

Max

Copy the formula cell, then select the destination cell, right-click on it >
Paste Special > check "Formulas" > Ok
 
N

Nad

Thanks for your reply.
But my problem is that i have to copy the formula more than thousand cells.
If i use this method, it means i have to copy+paste the formula more than
thousand (because i have to copy the formula one after other cell )times and
it will consume much time.

Please let me know if any other method to do that so that i can save my times.
 
M

Max

Ah, knew it couldn't be so simple. Pl post some specifics, what is the exact
formula in the starting cell you are trying to propagate, and in what manner
is it supposed to increment and/or skip action as you copy down, etc.

For example, if you have, say:

In C2: =IF(MOD(ROW(A1),2),SUM(A2:B2),"")

C2 copied down will return alternating cells which sum cols A and B,
viz you'd get the equivalent:
in C2: = SUM(A2:B2)
in C3: blank ("")
in C4: = SUM(A4:B4)
in C5: blank ("")
in C6: = SUM(A6:B6)
etc
 
N

Nad

Very Thanks Max, U are realy Max........imum.
This is realy what i want.
But i still have a problem.
This formula is working when i copy it to downward but when i copied it to
the right of the cell it is not working.
My formula is =IF(MOD(ROW(E3),2),Sheet1!$E3,"")
Please help.
Regards,
 
M

Max

=IF(MOD(ROW(E3),2),Sheet1!$E3,"")

Just replace ROW with COLUMN if you're copying across,
but I'm not sure how you want the "Sheet1!$E3" part to behave

Try out these 3 examples to see which returns what you're really after

=IF(MOD(COLUMN(E3),2),Sheet1!$E3,"")
returns Sheet1!$E3 in the cell, then when copied across will skip alternate
columns, and return only: Sheet1!$E3 since the $ fixes it to col E

while:
=IF(MOD(COLUMN(E3),2),Sheet1!E3,"")
returns Sheet1!E3 in the cell, then when copied across will skip alternate
columns and increment: Sheet1!E3 "horizontally" to return: Sheet1!G3, then:
Sheet1!I3, etc

and .. this variation:
=IF(MOD(COLUMN(E3),2),OFFSET(Sheet1!$E3,COLUMN(A1)-1,),"")
returns Sheet1!E3 in the cell, then when copied across will skip alternate
columns and increment: Sheet1!E3 "vertically" to return: Sheet1!E5, then:
Sheet1!E7, etc
 
P

Pete_UK

Why don't you just enter this formula in Sheet2 B2:

=IF(Sheet1!$E$3="","",Sheet1!$E$3)

Then copy this to the other cells where you want the formula to be
(D2, K2 ?) and then just edit them to:

=IF(Sheet1!$E$4="","",Sheet1!$E$4)
and
=IF(Sheet1!$E$5="","",Sheet1!$E$5)

Hope this helps.

Pete
 
M

Max

Ahh.., now much clearer
- that's the way you should have described it earlier <g>

In Sheet2,
Put in B2:
=IF(MOD(COLUMN(A1),2),OFFSET(Sheet1!$E$3,INT((COLUMN(A1)-1)/2),),"")
Copy B2 across to K2 ..
 
N

Nad

Thanks Max to give your valuable time to answer my question.
I have one other problem also so
I will send U my workbook on your address.
Please solve my problem when U get time.

Thanks to Pete also.
Regards,
 
M

Max

Nad said:
Thanks Max to give your valuable time to answer my question.
Click to expand...

Welcome ..
I have one other problem also so ..
Click to expand...

This thread is closed, you've got what you wanted. Put new queries in as
*new* posting/s. Make it one query per post, clearly described, attractive
for responders to respond.
I will send U my workbook on your address.
Please solve my problem when U get time.
Click to expand...

Pl *do not* send unsolicited files/replies to my private email.
Keep discussions within the newsgroup for the mutual benefit of all
 
Top