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,

Copy the formula cell, then select the destination cell, right-click on it
Paste Special check "Formulas" Ok
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
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,

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.


"Max" wrote:

Copy the formula cell, then select the destination cell, right-click on it
Paste Special check "Formulas" Ok
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
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,

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
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.

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,
"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
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.

=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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
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,"")

Sorry Max, I did'nt get my result.
Let me explain U again what i want.
I have data In Sheet1 / Column E3..E5
I want to put these data of Sheet1 In Sheet2(from B2..K2) leaving one cell.
In Sheet2
Cell B2 (data from Sheet1/E3)
c2 (Empty)
d2 (Data from Sheet1/E4)
...
Sorry Max, May be i confused U.
Regards
"Max" wrote:

=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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
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,"")

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

On Apr 3, 9:00 am, Nad wrote:
Sorry Max, I did'nt get my result.
Let me explain U again what i want.
I have data In Sheet1 / Column E3..E5
I want to put these data of Sheet1 In Sheet2(from B2..K2) leaving one cell.
In Sheet2
Cell B2 (data from Sheet1/E3)
c2 (Empty)
d2 (Data from Sheet1/E4)
...
Sorry Max, May be i confused U.
Regards



"Max" wrote:
=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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
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,"")- Hide quoted text -

- Show quoted text -

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((COLU MN(A1)-1)/2),),"")
Copy B2 across to K2 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
Sorry Max, I did'nt get my result.
Let me explain U again what i want.
I have data In Sheet1 / Column E3..E5
I want to put these data of Sheet1 In Sheet2(from B2..K2) leaving one cell.
In Sheet2
Cell B2 (data from Sheet1/E3)
c2 (Empty)
d2 (Data from Sheet1/E4)
...

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,

"Max" wrote:

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((COLU MN(A1)-1)/2),),"")
Copy B2 across to K2 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nad" wrote:
Sorry Max, I did'nt get my result.
Let me explain U again what i want.
I have data In Sheet1 / Column E3..E5
I want to put these data of Sheet1 In Sheet2(from B2..K2) leaving one cell.
In Sheet2
Cell B2 (data from Sheet1/E3)
c2 (Empty)
d2 (Data from Sheet1/E4)
...

"Nad" wrote:
Thanks Max to give your valuable time to answer my question.

Welcome ..

I have one other problem also so ..

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.

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---