+ Reply to Thread
Results 1 to 8 of 8

Cannot turn off formula audit mode on a single cell

  1. #1
    Registered User
    Join Date
    03-17-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Cannot turn off formula audit mode on a single cell

    I have a work book containing a summary page that lists the contents of multiple like forms in the same workbook at one line per form. Basically, the summary page shows names and address of people who have registered for an event.

    One cell in the summary page is displaying the formula, not the data from the form it's pointing to. I've pasted the format from other cells to this one cell to try and turn off the audit formula mode. If I change the cell pointer to another cell in the form, the data appears but when I change the cell pointer back, I get the formula again. It seems to be one specific cell ='Attendee 2'!$C$6

    This formula is in cell C3 of my summary page. It's pointing to a tab called Attendee 2, cell C6. No matter where I put this formula, I get the formula audit mode. If I change the pointer to ='Attendee 2'!$c$5, I get data. As soon as I change it back to c6, I get the formula. If I turn formula audit mode on for the sheet, all formulas appear, but, when I turn off the formula audit mode, everything goes back to showing data except this one cell. This is really hard to describe but does anyone have a clue as to what's going on?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Cannot turn off formula audit mode on a single cell

    Can you attach your file? If it has private data you can remove everything except this one cell, and the cell that it refers to.

    The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-17-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Cannot turn off formula audit mode on a single cell

    I followed your instructions and I think I attached the file. But, I can't see it so I'm not sure. The file contains the problem I'm describing. Thanks!
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Cannot turn off formula audit mode on a single cell

    Change the cell format to General, it is currently Text. Then F2 and enter
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Cannot turn off formula audit mode on a single cell

    Ford's response confirmed my suspicions, and I agree with his advice. However this threw me:
    Quote Originally Posted by bmaide View Post
    If I change the pointer to ='Attendee 2'!$c$5, I get data.
    I tried that, and that's not what happens. No matter what you change it to, the cell still displays a formula.

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Cannot turn off formula audit mode on a single cell

    Hi there,


    It seems to be one specific cell ='Attendee 2'!$C$6.

    This formula is in cell C3 of my summary page. It's pointing to a tab called Attendee 2, cell C6.


    I'm not sure about this. The same thing occurs when I edit (by pressing F2 and then Enter) ANY formula-containing cell which is formatted as Text (not only in column C!). If I change the NumberFormat of the column to General, select a cell in that column, and then press F2 followed by Enter, the formula result is displayed rather than the formula itself.

    I suspect that the worksheet formatting was applied AFTER the various formulas were entered.


    Hope this helps.

    Regards,

    Greg M

  7. #7
    Registered User
    Join Date
    03-17-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Cannot turn off formula audit mode on a single cell

    That worked. They were all text including this one cell when I started. I followed your instructions which resolved the problem but now the cell is text again as are all the others. Any clue how that happened in the first place? Thanks very much!!!

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Cannot turn off formula audit mode on a single cell

    Hi again,

    Many thanks for your feedback.

    Glad to hear that it worked for you.

    At this stage I can't really offer any suggestions as to how/why "the cell is text again as are all the others"

    Regards,

    Greg M

+ 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. Word RegEx turn off single line mode
    By ldoodle in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2018, 07:53 AM
  2. Turn Audit Trail on and off
    By kati.coy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2016, 11:34 AM
  3. How do I turn on design mode?
    By cupboy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-22-2015, 05:06 AM
  4. toggle to turn full screen mode on and off via onkey shortcut
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2010, 02:54 PM
  5. How show value of variable in single step debug mode?
    By Chet Shannon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2005, 03:00 AM
  6. how to turn off design mode
    By Paul in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-23-2005, 09:05 AM
  7. [SOLVED] How do I turn off [Group] mode?
    By tothemax in forum Excel General
    Replies: 3
    Last Post: 03-17-2005, 06:06 PM

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