+ Reply to Thread
Results 1 to 5 of 5

Excel 2003: Changing cell contents blows up User Defined Formula which uses that cell

  1. #1
    Registered User
    Join Date
    10-18-2007
    Posts
    28

    Excel 2003: Changing cell contents blows up User Defined Formula which uses that cell

    Cell X1 has a Formula, call it FF, which uses 2 user defined formulas, MAE, MSE:
    Code:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    MAE and MSE inputs in FF are


    Range RX: Col X from R2 to R500
    Range RY: Col Y from R2 to R500

    If I take any cell in RX and change its contents,
    I get a NAME? error in cell X1.

    If I click on FF, I see the formula which appears to be correct.

    Some other potential factors:

    • Each cell in RX is itself the results of a Formula: the Summation of 2 other Cells. Even if I double click and move the colored selection outline to another cell, FF gets the NAME? error.
    • This worksheet used Row 1 Labels in Formulas which at some point in time were lost for some unknown reason. Labels were replaced with traditional ranges in all formulas. But this "damage" occurred many revisions ago and there did not seem to be any problems since them. Whether this could be contributing, I don't know.
    • This workbook occasionally bombs and Excel does a recover on it.
    • I am using a PC which is unstable which occasionally freezes every few days.

    Any thoughts?

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,324

    Re: Excel 2003: Changing cell contents blows up User Defined Formula which uses that cell

    Hi,

    I think it's time to upload the workbook so we can see in context. This is too difficult to visualise from a description only.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-18-2007
    Posts
    28

    Re: Excel 2003: Changing cell contents blows up User Defined Formula which uses that cell

    OK, how do I upload the file?

    I was thinking. Isn't there a setting so that Macros update every time the spread sheet updates? Shift + F9?

    Because once I make that change, if I do an Evaluate, MSE and MAE come up as NAME? which means they are not recognized. And I've verified the Module is included with their code.

  4. #4
    Registered User
    Join Date
    10-18-2007
    Posts
    28

    Re: Excel 2003: Changing cell contents blows up User Defined Formula which uses that cell

    I'm wondering if it is the Macro Calculation Option reverting back to MANUAL since some workbooks could have a extremely long calculation time - and it might take 1 hour to open.

    So I wrote User Defined Functions. Are these treated as Macros when it comes to Calculation Options?

    My guess is NO since I'm getting the NAME? error.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Excel 2003: Changing cell contents blows up User Defined Formula which uses that cell

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 07-01-2013, 10:51 AM
  2. Replies: 5
    Last Post: 08-10-2012, 04:32 PM
  3. EXCEL 2003 copy paste a defined range in a cell
    By EXCELNOOB123 in forum Excel General
    Replies: 1
    Last Post: 03-07-2011, 10:01 PM
  4. Excel 2003 does not remember user defined options
    By mr_smurf in forum Excel General
    Replies: 2
    Last Post: 03-04-2010, 12:23 PM
  5. Add combo list with user defined data in selected cell in Excel 2003
    By sakthivel.s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2009, 05:45 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1