Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

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

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What's your formula?

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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?

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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.

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    EDIT:: ****, too slow.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-26 14:24 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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?

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •