Cell Display/Cell Format Assistance

    Hello, this is my first time posting. I have never been "formally" tought how to use Excel, except a beginner course, and have virtually NO experience in VBA. From reading this forum, I have gotten some basics though. I have mastered the basics of Excel by the process of "trial by fire" and found myself stuck.


    I was assigned a project here at work where I had to create (from scratch) and maintain a system to track a process. I have my main workbook that is 3000 rows and that grows by about 100 each week. I had a column ( 1 of 29) that began as a text column, but I was given directive to create a column that would keep track of wheter a document was sent to a certain office or not. I have been doing this by simply using a numeric column, and in the row that needed it, I entered "1". Then, at the bottom of that column I have a cell that has the basic formula to add all the cells in that column together. This has worked in 14 other columns. This one however does not want to run the formula, I have to go back and retype each "1" to get it to add (my formula being =sum(d2:d2988) ). This would be fine, but when ever I add the new rows for the week, I have to reformat and retype each and every cell in that column.


    Any advice on why Excel is not accepting the change from text to number? I have already tried making sure the page/column/cell is not protected, I have even tried deleting and recreating that column, but nothing seems to work. Any help would be appreciated. Thank you.


    Also, this one may have been asked before, but I was not able to find it in the first 10 pages of threads, I have a sheet that takes the number from my monthly stats and combines them onto one sheet for monthly/quarterly/yearly. It looks very confusing with all the "0" from the cells that are yet to be filled in by data due to it not being that month yet. How do I make a cell show blank instead of the "0" when a formula is already entered there?



    Liz :thanx:

    Liz
    :saychees:

    Don't know why you are having this problem summing your column, but the COUNTA function should give you the result you want. COUNTA "doesn't care" whether there the cell contents are text or numbers.


    As to your second question, you could use the IF function: something like
    =IF(SUM(monthly_Stat_range)=0,"",SUM(monthly_Stat_range))

    Thank you Fifijazz, yes, this problem is very irritating, I have a few other people here in the office working on it, but they are all stumped as well. I am just not looking forward to retyping this column. It is only "1", but like I said, it is at 3000 and counting right now. Also, I have My main workbook, then a yearly workbook with each month having its own worksheet, and this problem is consistent throughout all the worksheets, which is why I am assuming it is a formatting problem.


    Thanks again, I do appreciate it.

    Liz
    :saychees:

    I just wanted to add, a co-worker ran an error function and found out that it will not work because it is a text column not number or general, but I have changed it over and over again to general or number and it wont work. I dont know, but the =COUNTA formula is working beautifully. Thanks again.


    Liz

    Liz
    :saychees:

    I think you need to convert the text values to numeric. You can do this by formatting the column to general, then put the number 1 in a blank cell somewhere, copy it and then select the column in question and Paste Special|Multiply. Alternatively, after formatting as General, you could select the column and perform a Data|Text to Columns on it. Make sure you use fixed width and when you get the choice of data type, make sure it is General and not text.

    Seti,


    Thank you very much, I did the text to columns on this problem and it seems to have resolved the issue. Thanks again for your help!


    Liz

    Liz
    :saychees:

    Okay, now that I know that I can get it to work right, is there anyway to have this run on all worksheets in a workbook, i have one workbook with 12 worksheets in it, plus 3 other workbooks (each with one or two worksheets).


    Thanks.

    Liz
    :saychees:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login