+ Reply to Thread
Results 1 to 10 of 10

IF formula

Hybrid View

  1. 10-11-2010, 05:27 PM #1
    Hegemon
    Hegemon is offline
    Registered User
    Join Date
    10-11-2010
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    4

    IF formula

    Long time lurker, first time poster. I've used this forum for a long time and never had a question the search couldn't find. However, I have one now that I cannot find an answer to at all, so I finally had to register.

    I want a formula that will display text if an adjacent cell is being used in a formula.

    For example:

    If Cell A1 has the following formula: =A2+A3+A4

    I want a formula that I can put in B2, B3, B4, etc that will display some text when A2, A3, A4, etc are being used in the formula in A1.

    The reason for this is that the cells in the A1 formula are added manually over time. As I add data in A5, A6, and so on, I will go back to A1 and add those cells to my tally formula. The formula I am asking for will show me which cells are part of the A1 formula. Those without text by them still need to be added to the A1 formula.

    This is a simplification of the sheet I am working with. Suggestions to modify the formula in A1 will not work. I really need the formula I am asking for. If my post is confusing or if you need more info please ask. Thanks! I know you guys can help me out!
    Last edited by Hegemon; 10-11-2010 at 05:31 PM.
    Reply With QuoteRegister To Reply
  2. 10-11-2010, 07:10 PM #2
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: IF formula

    Wouldn't Formulas > Trace Precedents accomplish what you're looking for?
    Reply With QuoteRegister To Reply
  3. 10-11-2010, 08:44 PM #3
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,333

    Re: IF formula

    Hi Hegemon,

    I agree with Cutter. This function of Excel is already built in. Click on the Formulas tab and find the words Precedents. I think of this as Downstream or Upstream to the formula.
    There is a great picture of what his does on a site I visit often. See what it does at:
    http://www.dummies.com/how-to/conten...l-2007-fo.html
    Reply With QuoteRegister To Reply
  4. 10-11-2010, 09:16 PM #4
    Hegemon
    Hegemon is offline
    Registered User
    Join Date
    10-11-2010
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: IF formula

    Thanks for the suggestion, but that's not quite what I'm needing.

    I've been working on this, and I'm Really close to an answer. I can get this formula to work when selecting 1 cell, but I need it to work for a range of cells:

    =IF(ISERR(SEARCH(A10,A1)),"","TRUE")

    Where A1 contains the Formula that references cell A10. For my specific worksheet, I need to be able to search for A10 within a range of cells. The formula works for one cell, but doesn't work when I put in a range, such as:

    =IF(ISERR(SEARCH(A10,A1:A9)),"","TRUE")

    Can anyone shed light on this? I'm really close to getting what I need. Thanks for the suggestions so far!
    Reply With QuoteRegister To Reply
  5. 10-11-2010, 09:27 PM #5
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: IF formula

    Sorry to burst your bubble, but your formula does not look for the cell reference A10 in cell A1. It looks whether the value of A10 can be found in A1.

    Can you step back and explain what you are trying to do?
    Microsoft MVP
    http://www.teylyn.com
    Twitter: http://twitter.com/IngeborgNZ
    Reply With QuoteRegister To Reply
  6. 10-11-2010, 09:43 PM #6
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,333

    Re: IF formula

    Yeh!

    teylyn is back.

    How do we discribe to Hegemon that the Search (in his formula) is different than the Search in Search and Replace?
    Reply With QuoteRegister To Reply
  7. 10-11-2010, 09:41 PM #7
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,333

    Re: IF formula

    Hi Hegemon,

    I was thinking the Indirect() function was close to what you wanted. You want to look into a formula in a cell and see if the formula contains something like "C7". If you find "C7" in the formula you would then mark it somehow. There is no reverse of Indirect() unless someone else knows. You CAN search for text in formulas using Search. Then if you include Formulas and Find All it gives a list of where they were found.

    Look at the Search and Find All to see if that is what you want.
    Reply With QuoteRegister To Reply
  8. 10-11-2010, 10:06 PM #8
    Hegemon
    Hegemon is offline
    Registered User
    Join Date
    10-11-2010
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: IF formula

    teylyn - See the attached workbook for what I want. I want to be able to put a mark (in this case a little dot) beside the purchases on the right when they are being pulled in the group section on the left. The formula for the dot needs to be able to search D3:D27. Seems simple enough, but the workbook is a simplified version of a very complex sheet.

    That dot will save my sanity.
    Attached Files Attached Files
    Last edited by Hegemon; 10-11-2010 at 10:10 PM.
    Reply With QuoteRegister To Reply
  9. 10-11-2010, 10:32 PM #9
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: IF formula

    You won't be able to do this with formulae. You'll need VBA to read the formula, take it apart, identify the individual cell references and so on. Not an easy feat.

    Again, your dot is showing because I4 = 100 and D3 = 100, not because the formula in D3 contains a reference to I4.

    You seem to want to consolidate amounts from different tables. I'm just not quite sure I understand your data structure. It seems to be flowing in the wrong direction.
    Reply With QuoteRegister To Reply
  10. 10-11-2010, 10:49 PM #10
    Hegemon
    Hegemon is offline
    Registered User
    Join Date
    10-11-2010
    Location
    Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: IF formula

    Quote Originally Posted by teylyn View Post
    You won't be able to do this with formulae. You'll need VBA to read the formula, take it apart, identify the individual cell references and so on. Not an easy feat.

    Again, your dot is showing because I4 = 100 and D3 = 100, not because the formula in D3 contains a reference to I4.

    You seem to want to consolidate amounts from different tables. I'm just not quite sure I understand your data structure. It seems to be flowing in the wrong direction.
    It sounds like I just need to give up on the idea then. I am not allowed to change the sheet that much - I got permission to add my column, but I can't change the structure of the sheet or where things are on it. The flow is what it is.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1