Pasting only the formulas and not the data

[JM]

When I copy a row and then use Paste Special and select
the Formula radio button, the contents of cells
containing data are pasted along with the formulas. How
do I get only the formulas to paste without the data?

(By my reading, this is what is supposed to happen when I
follow the above process.)

 

[Dave Peterson]

Actually, if you look at the cell where you just pasted, you'll see the formula
(look in the formula bar).

But it sounds like the formula evaluated to the same value as before. How it
evaluates depends on what's in the cells the cells that the formula uses and
what the formula is.

If this makes little sense, you may want to post the formula, and what's in the
cells. And what you expected to be shown. (Just plain text--don't attach a
workbook.)

 

[JM]

Thanks, Dave.

Actually, I copied an entire row and pasted the row using
paste special and the formula option. Some of the
columns have text in them and others have formulas. I
expected the cells with text to be empty and the cells
with formulas to have the formulas in them. I understand
that the formula cells will be the same if the data that
they work on is the same. That's not the problem. The
problem is that I was expecting non-formula (text) cells
to be empty. Am I incorrect?

Here's an example:
A B
1 3 =A1*2
2

B1 evaluates to 6. If I cut and past special for
formulas from Row 1 to Row 2, I expect to get
A B
1 3 =A1*2
2 =A2*2
where A2 is empty and therefore, B2 evaluates to 0.
Instead, I get
A B
1 3 =A1*2
2 3 =A2*2
where B2 evaluates to 6.

Am I misunderstanding the paste special formula?

Jim

 

[RagDyeR]

I believe the OP is referring to actual data Dave.

If A1, A3, and A5 contain a formula, and A2 and A4 contain "Cat" & "Rat"
respectively, and you select A1:A5, and copy, paste special, formulas, the
Cat & Rat get copied also.

I believe that's the question.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Actually, if you look at the cell where you just pasted, you'll see the
formula
(look in the formula bar).

But it sounds like the formula evaluated to the same value as before. How
it
evaluates depends on what's in the cells the cells that the formula uses and
what the formula is.

If this makes little sense, you may want to post the formula, and what's in
the
cells. And what you expected to be shown. (Just plain text--don't attach a
workbook.)

 

[Dave Peterson]

Ahhhh.

I believe the OP is referring to actual data Dave.

If A1, A3, and A5 contain a formula, and A2 and A4 contain "Cat" & "Rat"
respectively, and you select A1:A5, and copy, paste special, formulas, the
Cat & Rat get copied also.

I believe that's the question.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Actually, if you look at the cell where you just pasted, you'll see the
formula
(look in the formula bar).

But it sounds like the formula evaluated to the same value as before. How
it
evaluates depends on what's in the cells the cells that the formula uses and
what the formula is.

If this makes little sense, you may want to post the formula, and what's in
the
cells. And what you expected to be shown. (Just plain text--don't attach a
workbook.)

 

[Dave Peterson]

Oops.

After you paste your data/formulas, you can select that pasted range and do:
Edit|goto (or ctrl-G or F5)
click Special
Select Constants
Click ok.

Now just the cells with values (no formulas) are selected. Hit the delete key
(or edit|clear|Contents).

Sorry about the confusion (mine).

 

[Peo Sjoblom]

You can do that, select A1:A5, press F5, click special, select formulas,
copy, do edit>paste special as formulas and number formats
Interestingly if you just paste with ctrl + V it pastes then as values
if there are more than one formula not aligned with the other formula(s)

 

[Guest]

Thanks, Dave.

-----Original Message-----
Oops.

After you paste your data/formulas, you can select that pasted range and do:
Edit|goto (or ctrl-G or F5)
click Special
Select Constants
Click ok.

Now just the cells with values (no formulas) are selected. Hit the delete key
(or edit|clear|Contents).

Sorry about the confusion (mine).




--

Dave Peterson
(e-mail address removed)
.

 

[David McRitchie]

Your post is ambiguously interesting,
You were looking to paste only the formulas and not the text when
you copied a row and pasted and Dave Peterson gave you the
answer with ...

F5, click special, select formulas,
copy, do edit>paste special as formulas and number formats

I expect you want to do this a lot and I have a macro for doing this

Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

But the ambiguities alluded to are interesting as well, one of the
first things that came to my mind were pasting a formula as the
formula without being adjusted as to where it is pasted.
Chip Pearson has a right click menu to paste a formula, used on a
right-click (context) menu.
http://www.google.com/groups?threadm=uiqh89AxAHA.1620@tkmsftngp05
http://www.cpearson.com/excel/menus.htm

And to see the formula of another cell there is my GetFormula
http://www.mvps.org/dmcritchie/excel/formula.htm

 

[RagDyer]

<<"one of the first things that came to my mind were pasting a formula as
the formula without being adjusted as to where it is pasted.">>

Isn't this easily accomplished David, by simply copying the entire formula
from the formula bar, then <Enter>, then pasting in any cell or any number
of cells, where the formula retains it's original references?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Your post is ambiguously interesting,
You were looking to paste only the formulas and not the text when
you copied a row and pasted and Dave Peterson gave you the
answer with ...

F5, click special, select formulas,
copy, do edit>paste special as formulas and number formats

I expect you want to do this a lot and I have a macro for doing this

Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

But the ambiguities alluded to are interesting as well, one of the
first things that came to my mind were pasting a formula as the
formula without being adjusted as to where it is pasted.
Chip Pearson has a right click menu to paste a formula, used on a
right-click (context) menu.
http://www.google.com/groups?threadm=uiqh89AxAHA.1620@tkmsftngp05
http://www.cpearson.com/excel/menus.htm

And to see the formula of another cell there is my GetFormula
http://www.mvps.org/dmcritchie/excel/formula.htm

 

[David McRitchie]

Same thing but I think the User Defined Function for the
right click menu goes faster and I think it would handle a range of cells.