excel formulae

[philb99]

Hi - having an issue copying excel formulae from tab = Interest which has linked formulae to a tab=Assets to a new sheet.

I have tried Paste Special but doesn't like it

On the Interest tab two of the formulae are

=SUM(Assets!AT5:AT6)
=SUM(Assets!AT11:AT12)+Assets!AT15

 

[Joe4]

What is the issue, exactly?
What is happening, and what is your expected result?

 

[awoohaw]

what is the error message? the error may be in your data and not in your formula?

sharing the data in the cells and and expected outcome would be helpful.

 

[philb99]

What is the issue, exactly?
What is happening, and what is your expected result?

I wish to copy the formula - =SUM(Assets!AT5:AT6) into a new sheet

When doing so I get the following reported #REF!

Therefore when i click into the cell the following is displayed
=SUM(Assets!#REF!)

 

[philb99]

what is the error message? the error may be in your data and not in your formula?

sharing the data in the cells and and expected outcome would be helpful.

error message #REF!

Therefore when I click into the cell the following is displayed
=SUM(Assets!#REF!)

 

[Joe4]

What cell address are you copying the formula from?
And what cell address are you copying the formula to?
Excel automatically tries to adjust the formula to offset the difference in the ranges.

If you want the EXACT same formula, one trick is to not simply copy/paste the cell, but go into the formula, and select and copy everything after the "=".
So you are just really copying the string:

SUM(Assets!AT5:AT6)

Then, go to where you want to paste it, type in the "=" and then paste string you have in copy mode.

Since you are copying/pasting a string (and not a formula) in that case, it will not alter it.
But putting the "=" in front of it makes it a formula again.

 

[awoohaw]

error message #REF!

Therefore when I click into the cell the following is displayed
=SUM(Assets!#REF!)

To add onto what @Joe4 wrote. One meaning of the REF# error is to notify you the way that excel tries to find that cell is not possible. So, if you were copying a formula from cell C5 that references cell B3. B3 in reference to cell C5 is two cells up and and on cell to the left. So, if you copy the formula of C5 into a new sheet into cell A1... how can excel find a cell one to the left and two up... The cell doesn't exist!

To show this: Type the word "Green" into a worksheet in cell A5, then in cell D7 type "=A5". Now try to copy cell D7 onto a new worksheet in cell A1.

 

[philb99]

What cell address are you copying the formula from?
And what cell address are you copying the formula to?
Excel automatically tries to adjust the formula to offset the difference in the ranges.

If you want the EXACT same formula, one trick is to not simply copy/paste the cell, but go into the formula, and select and copy everything after the "=".
So you are just really copying the string:

SUM(Assets!AT5:AT6)

Then, go to where you want to paste it, type in the "=" and then paste string you have in copy mode.

Since you are copying/pasting a string (and not a formula) in that case, it will not alter it.
But putting the "=" in front of it makes it a formula again.

perfect thanks - is there anyway to get around this if I had 20 cells to copy over

 

[Joe4]

perfect thanks - is there anyway to get around this if I had 20 cells to copy over

Is there any pattern, rhyme or reason to where these cells are coming from and where they are being copied to?
Are they all grouped together?

Possibly may be able to use VBA, depending on your answers.

But maybe let's take a step back.
Why not just reference the cell that has the formula you want the value from?

For example, let's say that the formula:

=SUM(Assets!AT5:AT6)

is found on Sheet1 cell A1.

Then, if you want that same value on Sheet2, why not just reference that cell like this?

=Sheet1!A1

You don't need the underlying the formula if you simply reference the cell that contains that formula.