#REF how to delete a cell and not change the formulas contai

[chadm]

I have two worksheets. One has formulas based on data in the other.

How can I delete a cell of data - i.e. it is not needed, but not have to change the formula in every cell?

I can I could o out the cells and hide them, but I would rather get rid of the reference to the cell altogther.

 

[Mark W.]

What's your formula?

 

[Aladin Akyurek]

On 2002-04-26 14:19, chadm wrote:
I have two worksheets. One has formulas based on data in the other.

How can I delete a cell of data - i.e. it is not needed, but not have to change the formula in every cell?

I can I could o out the cells and hide them, but I would rather get rid of the reference to the cell altogther.

What is the formula?

 

[chadm]

=K282+'Master Illustration'!D76-'Master Illustration'!E76+'Master Illustration'!#REF!*'Master Illustration'!$G$95

This formula increase by 1 every fourth cell - One sheet summarizes annual data, the other is quarterly. I can not just copy down, as every fourth cell increases by 1, not every cell.

 

[Mark O'Brien]

What is the formula? (couldn't resist, as always)

EDIT:: ****, too slow.

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-26 14:24

 

[Aladin Akyurek]

On 2002-04-26 14:23, chadm wrote:
=K282+'Master Illustration'!D76-'Master Illustration'!E76+'Master Illustration'!#REF!*'Master Illustration'!$G$95

This formula increase by 1 every fourth cell - One sheet summarizes annual data, the other is quarterly. I can not just copy down, as every fourth cell increases by 1, not every cell.

What is the first cell address housing this formula and in which when copied down you get the #REF! error?

 

[chadm]

Let me try and elaborate.

Sheet1 has annual data, Sheet2, quarterly.

Sheet2 does quarterly calculations based on data enetered in Sheet1.

Sheet1 also summarizes quarterly data and calculations on Sheet2.

If I want to change a formula on Sheet2, I can not just copy it down, as every 4th Cell has to go to Sheet1 and get the data in the next row.

Now I have deleted one of the columns on Sheet1 - Now every 4th cell on Sheet2 has #REF - I do not want to go and change the formula in every 4th cell again and increase the row reference by 1.

Anyone have any sort of idea of what I am not very effectively trying to explain?

 

[Aladin Akyurek]

On 2002-04-26 14:36, chadm wrote:
Let me try and elaborate.

Sheet1 has annual data, Sheet2, quarterly.

Sheet2 does quarterly calculations based on data enetered in Sheet1.

Sheet1 also summarizes quarterly data and calculations on Sheet2.

If I want to change a formula on Sheet2, I can not just copy it down, as every 4th Cell has to go to Sheet1 and get the data in the next row.

Now I have deleted one of the columns on Sheet1 - Now every 4th cell on Sheet2 has #REF - I do not want to go and change the formula in every 4th cell again and increase the row reference by 1.

Anyone have any sort of idea of what I am not very effectively trying to explain?

Try using ASAP-Utilities to de-link formulas. It is free downloadable at;

http://asap-utilties.com

Aladin

 

[Chris Davison]

On 2002-04-26 14:23, chadm wrote:
=K282+'Master Illustration'!D76-'Master Illustration'!E76+'Master Illustration'!#REF!*'Master Illustration'!$G$95

This formula increase by 1 every fourth cell - One sheet summarizes annual data, the other is quarterly. I can not just copy down, as every fourth cell increases by 1, not every cell.

would we be right in assuming you want :

=K282+'Master Illustration'!D76-'Master Illustration'!E76+'Master Illustration'!F76*'Master Illustration'!$G$95

and then 4 rows down, you want :

=K283+'Master Illustration'!D77-'Master Illustration'!E77+'Master Illustration'!F77*'Master Illustration'!$G$95

if it's something like this, give us the ROW number that houses the first formula that's giving you the #REF error and we can solve this pretty quickly with a formula that you can then copy down quickly to every 4th row without any manual editting....

etc etc ?



edit - ooops, re-reading, I see Aladin's already asked that question. My apologies.
This message was edited by Chris Davison on 2002-04-26 18:47