Results 1 to 12 of 12
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell won't take formula (Excel XP)

    Your attachment hasn't made it. You can edit your post to add it. Please make sure that it is less than 100 KB in size (you can zip it if it's too big), and that you don't preview your post after filling the "attach a file" box.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell won't take formula (Excel XP)

    Change the cell format to "General". If you enter a formula into a cell that is formatted as Text, the formula will be entered as a text value, not a formula.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Cell won't take formula (Excel XP)

    I've got a weird "glitch" or problem in the attached worksheet.

    If you look at cells AI64 and AI66 you will see they each should contain a simple formula.

    AI64 is =C8. If I retype that simple formla it will enter the cell as it should every time.

    However, if you look at cell AI66 you will see that it has a text entry of = C14. No matter what I do it enters a text version every time I type it instead of applying the formula.

    Is this something in my formatting of the cell? A glitch in the program? Something else going on?

    The file was too bit to attach so I'm uploading it to: http://www.curvedmouldings.com/downl...yout_Sheet.xls

    I run up to date Norton Antivirus software on my system so the file should be clean.

    Thanks again for your help.

    BH Davis

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell won't take formula (Excel XP)

    Hans,

    When I attached the worksheet I got a "file too large " error. I didn't think to try zipping it! It's attached to the original post now. Or youc can download it from:



    http://www.curvedmouldings.com/downl...yout_Sheet.xls



    Legare,

    I did discover that it needs to be formatted as GENERAL. However, after formatting the cell as GENERAL, and entering the formula "=c14", it enters the text in the cell and when I look at the formatting it has gone back to formatted as TEXT.

    Thanks,

    BH Davis

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell won't take formula (Excel XP)

    If you unprotect the worksheet, format the cells as general, then protect the worksheet again, the formulas should work - they do for me.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell won't take formula (Excel XP)

    Hans,

    I'm beginning to think there is something really screwy with this.

    I've messed with protection etc.. No matter what I do it iis hit or miss getting the GENERAL formatting to stay and the formula to apply.

    If the formula is applied and I type over it with a different destination cell (say it was =c14 and I change it to =c16) it defaults back to TEXT and I loose the formula.

    I can go to cells in the main area of the worksheet (D27 as an example) and change the formula as many times as I want. Each time it just enters the new formula in the cell.

    However, out in that AI range where my labels are if I repeately change the formula in one of the cells it will sooner or later default to TEXT formatting.

    Go figure!

    Thanks for taking a look though.

    Regards,
    BH Davis

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell won't take formula (Excel XP)

    Like Hans, if I unprotect the sheet, change the format to General, enter a new formula, protect the worksheet, and then save the workbook it works as I would expect. Have you tried it on any other system. Do you have any addins installed that might be causing the problem? You might take a look at Jan Karel's Systematic Approach to Behavioral Problems in XL .
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell won't take formula (Excel XP)

    The reason for this is the inconsistent formatting of your worksheet. Cell C14 is in fact a merged cell, and at least one of the constituting cells is formatted as text. Apparently, Excel copies this formatting if you enter the formula =C14 elsewhere. You can do the following:
    - Unmerge cell C14
    - Set the number format for the constituting cells to General.
    - Merge the cells again.
    - Apply General formatting to cell AI66.
    - You should now be able to enter or edit the formula.

    This is one of the reasons I never use merged cells. They can easily lead to unpredictable behavior.

    Note: why does your workbook contain 45 modules, of which most are empty or contain no useful code? When I removed them, the workbook was well below 100 KB.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell won't take formula (Excel XP)

    Hans,

    Thanks, I'll take a look at some of those merged cells as you suggest.

    As to the size of this thing it is a very old spreadsheet. I started developing it close to 15 years ago in a DOS program called "As Easy As". When I originally brought it into Excel it was imported in and that sort of "went the way it went" so to speak. I spent some time getting it working back then and have been modifying it over the years as needed. I suppose it would be a good time to do a complete cleanup of the thing, but it works you know....................

    As I'm sure you've figured out I'm not even remotely thoroughly versed in this stuff. I can figure most of it out and get it to do what I need, but that doesn't mean I'm really doing it right. I guess you could say I'm one of those that know just enough to get into trouble!

    Thanks again for your help. I'll try to spend a little time cleaning this thing up.

    Regards,
    BH Davis

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell won't take formula (Excel XP)

    Hans,

    I've taken a look at the modules I have in the worksheet. In VISUAL BASIC with the worksheet open I went to VIEW, PROJECT EXPLORER. In the explorer window I found the approx. 45 modules to which you referred.

    After double clicking on each one is it okay to just right click and select REMOVE MODULE # on each one that is empty when selected? As long as it has no code it has no effect on the spreadsheet? Aslo, how could so many empty modules have been created? Is one generated every time I run TOOLS, MACROS, CREATE NEW MACRO? I do that a lot to test things I want to try.

    Thank you for your time and knowledge........it is genuinely appreciated !
    BH Davis

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell won't take formula (Excel XP)

    You can right-click a module, select Remove and click No in the prompt to export it before removing. If the module is empty, or if it contains code you never use, it will have no effect on the workbook except decreasing the file size.

    The empty modules were probably created by selecting Insert | Module in the Visual Basic Editor, but it's impossible to tell for sure.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell won't take formula (Excel XP)

    Recording a macro often adds a new module indeed. Not sure about creating one from the macros dialog, but I expect the same thing may happen.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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