+ 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
    4

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,097

    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

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    If someone helped you please click on the star icon at the bottom of their post
    If your problem is solved please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

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

    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 2000/3/7/10/13/16
    Posts
    46,728

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,097

    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
    Posts
    2,518

    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
    4

    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
    Posts
    2,518

    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)

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