+ Reply to Thread
Results 1 to 9 of 9

Identify active cell and use the column to add formula to another cell

  1. #1
    Registered User
    Join Date
    03-24-2006
    Location
    Stafford, England
    MS-Off Ver
    Office 2003
    Posts
    70

    Identify active cell and use the column to add formula to another cell

    I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted.

    I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank.

    What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added.

    Hopefully that is not too confusing.

    In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].

    I am new to Excel VBA (Usually Access) so any help would be appreciated.

    Thanks. Jonathan
    Last edited by NBVC; 12-01-2009 at 01:46 PM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,098

    Re: Identify active cell and use the column to add formula to another cell

    No code required. Just amend your formula.

    B11: =IF(COUNT(B5:B10)=0,NA(),SUM(B5:B10))
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    03-24-2006
    Location
    Stafford, England
    MS-Off Ver
    Office 2003
    Posts
    70

    Re: Identify active cell and use the column to add formula to another cell

    Andy, thanks for the reply.

    However, this shows the #N/A error in the cell. Any idea how I can show this as blank?

    Thanks
    Jonathan

  4. #4
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Identify active cell and use the column to add formula to another cell

    Andy Pope has written this to give taht error message -- maybe this

    =IF(COUNT(B5:B10)=0,"",SUM(B5:B10))

    jiuk

  5. #5
    Registered User
    Join Date
    03-24-2006
    Location
    Stafford, England
    MS-Off Ver
    Office 2003
    Posts
    70

    Re: Identify active cell and use the column to add formula to another cell

    JIUK,

    This then puts me back in the position of it plots the "" as a zero on the chart.

    Any other ideas?

    Jonathan

  6. #6
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Identify active cell and use the column to add formula to another cell

    Use Andys code -- it will show NA but not in Your chart

    Have you tried / played?

    Jack in the UK

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Identify active cell and use the column to add formula to another cell

    Quote Originally Posted by JonPugh View Post
    Andy, thanks for the reply.

    However, this shows the #N/A error in the cell. Any idea how I can show this as blank?

    Thanks
    Jonathan
    To show the #N/A as Blank, you can use Conditional Formatting to "hide" the value.

    E.g

    Select the range and then go to Format|Conditional Formatting

    Select Formula Is from the 1st drop down and enter formula

    =ISNA(B11)

    where B11 is the top most cell in the selection you are highlighting.

    Click Format

    and from the Font menu, choose colour to match the background cell colour.

    Clikc Ok

    Click Ok to finish.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,098

    Re: Identify active cell and use the column to add formula to another cell

    Either use another range for the chart data source. You can hide this if you unset the plot visible cells only option.

    Or use conditional formatting to test cell for NA and set font to same colour as cell fill.

    As far as the charts are concerned #N/A is the only way to get a cell that contains information to not plot. As all non numeric values, other than #N/A, are treated as zero.

  9. #9
    Registered User
    Join Date
    03-24-2006
    Location
    Stafford, England
    MS-Off Ver
    Office 2003
    Posts
    70

    Re: Identify active cell and use the column to add formula to another cell

    Thanks to you both. I have used th econditional formatting.

    Jonathan

+ 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