paste special - formulas

[Guest]

I thought the intention behind Paste Special - Fomulas was to copy across
only the contents of cells that contain a fomula (i.e. begins with an '='
sign) and NOT those that contain a value. Have I got this wrong? It certainly
doesn't work that way for me since both formulas and values appear in the
paste area.

 

[Niek Otten]

No, that is not the intention. What you require can be achieved via Edit>Go
to>Special, check Formulas and then copy and paste.

 

[Guest]

Niek,
Many thanks, great idea. I had no idea what the GoTo function did until now.
I will definitely use it in future.

However, it doesn't quite solve my current problem in that my formulas are
spread out over multiple places in the worksheet and following the GoTo you
can't copy them all together in a single copy command. If I copy/paste them
individually it's almost the same as having to manually update each
individual formula in the target area.

You seem very definite about the function of Paste Special and yet the doc
seems clear that clicking the formula button only "pastes formulas as entered
in the formula bar" and elsewhere formulas are defined as expressions
starting with an '='. Yet this is not how it works in practice.

Could this be a bug and not a feature?

Thanks anyway for your suggestion.

Best regards,
Brian.

 

[Niek Otten]

You're right, you can't copy it in one command. Sorry for that. But you can
clear multiple selections, which leads me to this alternative:

Copy the whole range. Goto>Special>Constants, Edit>Clear all.

Be careful with your original constants!

Not all definitions in Excel are quite accurate. Sometimes a number is
considered a formula, sometimes it isn't.
You see the numbers in the formula bar too.
I think "formulas" is used here as opposed to "values", meaning results of
formulas.

 

[Guest]

Thanks again Nick, really appreciate your efforts, but this doesn't quite
crack it either.

Think I did as you suggested and having highlighted and then deleted the
constants tried to copy the residual formulas to the target area with "skip
blanks" specified. This, too, did not work as I expected, since the blank
areas from the copy area overwrote fields in the paste area, which I wanted
to retain.

Maybe I should just accept I've gotta do it longhand.

Cheers,
Brian.

 

[vezerid]

Brian,

Could this UDF be of help?

Function CellHasFormula(c as Range)
If Left(c.FormulaR1C1,1) = "=" Then
CellHasFormula = True
Else
CellHasFormula = False
Endif
End Function

If your sheet layout allows it, you can populate helper columns with
=CellHasFormula(A1)
and then filter on True/False.

HTH
Kostis Vezerides

 

[Guest]

Vezerid,

Many thanks for the suggestion. I do not think it takes me any further,
although I am certainly learning a lot of new things about Excel! If I
explain what I am trying to do it may make it clearer.

For UK tax purposes I have 9 Excel workbooks (one for each year from 1998 to
2006) with 6 separate worksheets in each workbook. These are all linked to
another spreadsheet that summarises the key elements of all of the data into
a single page. I have built these spreadsheets over some time and modified
them as I have become clearer about the rules governing the calculation of
income tax.

Now that I think I have a fully correct version for the tax year 2005/6 I
would like to copy the formulas from this year into the appropriate places of
all of the workbooks for earlier years without overwriting the constant
values (e.g. tax allowances, earnings figures etc) that are an essential part
of each worksheet. All of the worksheets have the same basic layout, so
Copy/Paste would be fine, if Paste Special - Formulas copied only formulas
and not constants, as I had thought that it would.

So, as I said before, it looks like the changes are going have to be done
individually, which is bound to introduce its own errors, but then nobody
ever said it was a perfect world!

Cheers,
Brian.