+ Reply to Thread
Results 1 to 3 of 3

Use an existing cell formula for creating a new more complex formula

  1. #1
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Use an existing cell formula for creating a new more complex formula

    I have in Cell AD8 the following reference
    =F14
    The cell F14 is blank and the result is 0. But, because I am using this in a league table scenario I don't want to see a 0 when there is no entry in F14

    I'd like to be able to update the formula somehow (not manually) to
    =if(F14="","",F14)

    That way, a blank cell F14 doesn't count in a =COUNTIF(F14:AA14,"=0") scenario, but an entry of 0 in F14 would be counted.
    I'd like to be able to use this down rows and across many columns, as there's about 1100 cells to be updated, and really don't want to go back and do this manually

    Can anybody point me towards a module to accomplish this?

    Hope that makes sense.

  2. #2
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: Use an existing cell formula for creating a new more complex formula

    I suppose an easy way would be if someone could provide how to make this workable:

    Cell AD8: =F14

    Cell AD108: =if("formula from AD8="","","formula from AD8)

    hope that helps or simplifies

  3. #3
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: Use an existing cell formula for creating a new more complex formula

    Hi there,
    maybe in AD108:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    (but this copy the result of formula in AD8, not the formula itself)
    Regards
    Miroslav R.

    (If You like my solutions, feel free to add reputation.)

  4. #4
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: Use an existing cell formula for creating a new more complex formula

    answered, and working, from another medium - but thanks all experts who considered...
    I don't think he will mind me sharing this as he is a volunteer in his field, and the solutions is now in the public domain - so here's the solution:

    Sub FixFormula()
    Dim cell As Range, s As String
    For Each cell In Selection
    If cell.HasFormula Then
    s = cell.Formula
    If Left(s, 1) = "=" Then
    s = Mid(s, 2, 255)
    cell.Formula = "=IF(" & s & "="""",""""," & s & ")"
    End If
    End If
    Next
    End Sub
    Last edited by dubcap01; 06-23-2014 at 07:42 AM. Reason: solved

+ 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. [SOLVED] SOLVED*****Beginer questions: creating a formula in an existing table
    By HarveyMushman in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-10-2014, 02:49 AM
  2. Formula needed based on 2 conditions & variable row reference
    By fantoosh420 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-11-2014, 05:46 AM
  3. Creating a complex formula using 2010
    By airedale360 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-03-2013, 02:02 PM
  4. Adding IF function to existing (complex) Formula
    By NDRYNWA in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-24-2013, 06:56 AM
  5. Replies: 1
    Last Post: 11-21-2012, 02:03 AM

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