+ Post New Thread
Results 1 to 6 of 6
Office Software Thread, Excel - Changing cell reference in Technical; Hello, I have a maths teacher who tells me: He has a spreadsheet with formulas. Within one of the formulas ...
  1. #1

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Excel - Changing cell reference

    Hello,

    I have a maths teacher who tells me:

    He has a spreadsheet with formulas. Within one of the formulas there is a cell reference to another cell in the same row.

    After doing some filtering on the spreadsheet he realises that the cell reference in the formula has changed to another cell in a different row, thus returning the wrong result.

    I can't replicate this problem. Has anybody come across this before and do you know what causes it?

    Thanks a lot,

    Ben

  2. #2

    Theblacksheep's Avatar
    Join Date
    Feb 2008
    Location
    In a house.
    Posts
    1,964
    Thank Post
    139
    Thanked 291 Times in 211 Posts
    Rep Power
    193
    I've only had it with user errors.

    Locking the cells that dont need to be changed fixed it in my cases, the user couldnt modify them anymore (even tho they said they didnt in the first place).

  3. #3
    leco's Avatar
    Join Date
    Nov 2006
    Location
    West Yorkshire
    Posts
    2,026
    Thank Post
    595
    Thanked 125 Times in 119 Posts
    Rep Power
    42
    Quote Originally Posted by Theblacksheep View Post
    I've only had it with user errors.

    Locking the cells that dont need to be changed fixed it in my cases, the user couldnt modify them anymore (even tho they said they didnt in the first place).
    Similar experiences here and agree locking cells worked. Users often "forget" that they have moved, copied or deleted something leading to errors that "cannot be explained".

  4. #4

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0
    OK - replicated the problem.

    It doesn't happen when you filter the data but when you sort data. The cell references with the formulas are corrupted.

    There are threads on the web relating to this so I'll have a trawl through.

    Thanks again.

    Ben
    Last edited by park_bench; 1st May 2009 at 04:55 PM.

  5. #5
    leco's Avatar
    Join Date
    Nov 2006
    Location
    West Yorkshire
    Posts
    2,026
    Thank Post
    595
    Thanked 125 Times in 119 Posts
    Rep Power
    42
    Quote Originally Posted by park_bench View Post
    OK - replicated the problem.
    It doesn't happen when you filter the data but when you sort data. The cell references with the formulas are corrupted.
    Ben
    The references will get changed when sorting if they are relative and not absolute.

  6. #6

    Join Date
    Nov 2006
    Location
    Lancashire
    Posts
    95
    Thank Post
    34
    Thanked 1 Time in 1 Post
    Rep Power
    0
    The problem occurs when you have formulas referencing cells on another worksheet and even occurs if you have absolute references.

    The way I worked around it was to pull the result of the referenced cell from the other worksheet onto the same worksheet. Then change the the offending formula to reference the result on the same worksheet.

    Ben



SHARE:
+ Post New Thread

Similar Threads

  1. Cell Phone signal.
    By laserblazer in forum General Chat
    Replies: 10
    Last Post: 27th June 2008, 10:12 AM
  2. Replies: 9
    Last Post: 17th June 2008, 09:49 PM
  3. Free stuff - online reference works
    By SpuffMonkey in forum Educational Software
    Replies: 0
    Last Post: 16th March 2007, 01:45 PM
  4. Is there much of a market for old reference material??
    By sidewinder in forum General Chat
    Replies: 4
    Last Post: 28th February 2007, 11:58 AM
  5. Excel Cell colour changes
    By wesleyw in forum How do you do....it?
    Replies: 3
    Last Post: 20th December 2006, 11:01 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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