+ Reply to Thread
Results 1 to 5 of 5

Create a nesting formula w/ multiple conditions based on fields w/VLOOKUP

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Create a nesting formula w/ multiple conditions based on fields w/VLOOKUP

    I have an excel 2007 formula/macro that is far to complex for me to create. Here is what I am trying to do.

    I have a workbook that has multiple sheet ties for auto filling. I want to look in column/field C66 and if (RD) is part of the text I want S66 to be 0 otherwise =R66. NOTE: the text that appear in the cells of column C are as a result of an automated population from the below VLOOKUP formula;

    =IF(B65="","",VLOOKUP(B65,'Combined Price Master 1-12-2011'!A1:G3246,2,FALSE))
    Is this achievable? I want to drag this formula down to Cell S79.
    Last edited by NiqueDomie; 01-13-2011 at 10:00 AM. Reason: Title did not meet rule criteria

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208

    Re: Complex Excel2007 Formula/Macro

    Not sure I totally understand, but try this in S66 and copy down:

    =IF(ISERROR(SEARCH("RD",C66)),R66,0)

    HTH,
    Jason

  3. #3
    Registered User
    Join Date
    01-12-2011
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Create a nesting formula w/ multiple conditions based on fields w/VLOOKUP

    I am making a sad attempt to build a complex formula or code I guess. Here is what I have:

    Column C list a discription; the discription is a result of the below VLOOKUP code referencing a worksheet within the workbook:

    =IF(B67="","",VLOOKUP(B67,'Combined Price Master 1-12-2011'!A3:G3248,2,FALSE))

    Some of my descriptions include a ** which denotes a product type, see below example:

    Bridge Unit BU3020 **

    I have additional columns to the right that are calculating cost. My ultimate goal is to have any product with the ** roll up as a total dollar amount to the first line item in a column on the far right. So in column R I would like a formula that goes back to column C, looks for the **, if the ** is there enters the dollar amount listed in column Q and if the ** is not listed in the description enters 0.

    Because this seems to be too many conditions I decided to build the final result one column at a time. In Column S I will take the sum of R65-R79. All of the other rows in S will equal the adjacent cell in R, in the end resulting in $0 listed for products in the same row that contain a ** and the actual dollar of all other products. The products with the ** will be part of the combined total of in S65 based on sum of R65-R79.

    I believe my formula hang up has to do with the fact that the original reference fields C66-C79 are the result of a formula, thus not true text or value, am I correct??? Can I get around this??
    Last edited by NiqueDomie; 01-13-2011 at 12:33 PM. Reason: Re-state problem with more clarity

  4. #4
    Registered User
    Join Date
    01-12-2011
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Complex Excel2007 Formula/Macro

    Quote Originally Posted by jasoncw View Post
    Not sure I totally understand, but try this in S66 and copy down:

    =IF(ISERROR(SEARCH("RD",C66)),R66,0)

    HTH,
    Jason
    Thanks Jasoncw, but that did not work. Did you by chance see my last explination?

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

    Re: Create a nesting formula w/ multiple conditions based on fields w/VLOOKUP

    jasoncw is not regularly online, so perhaps I can try to help you...

    If you are summing values in a column based on another column have text with ** at the end, then you can do it with one formula (Sumif).

    e.g.

    =SUMIF(C66:C79,"*~*~*",Q66:Q79)

    Does that work or do you still want to do it on individual lines?
    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.

+ 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