Results 1 to 9 of 9

embedded formula

This is a discussion on embedded formula within the Excel Questions forums, part of the Question Forums category; Hi, I am trying to create a formula that reads: If cell "b99" is blank, then leave it blank, otherwise, ...

  1. #1
    Board Regular
    Join Date
    May 2009
    Posts
    58

    Default embedded formula

    Hi,

    I am trying to create a formula that reads:

    If cell "b99" is blank, then leave it blank, otherwise, look up cell "b99" in the defined range of "Acct_budget_lookup,give me the value in column 11, for an exact match.

    My formula (which isn't working) is:
    =if(b99,"","",VLOOKUP($B99,Acct_budget_lookup,11,FALSE))

    Could anyone tell me what I am doing wrong?

    Thanks
    Darcy

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    6,779

    Default Re: embedded formula

    Quote Originally Posted by darcya View Post
    Hi,

    I am trying to create a formula that reads:

    If cell "b99" is blank, then leave it blank, otherwise, look up cell "b99" in the defined range of "Acct_budget_lookup,give me the value in column 11, for an exact match.

    My formula (which isn't working) is:
    =if(b99,"","",VLOOKUP($B99,Acct_budget_lookup,11,FALSE))

    Could anyone tell me what I am doing wrong?

    Thanks
    Darcy
    What error are you seeing?

  3. #3
    Board Regular
    Join Date
    May 2009
    Posts
    58

    Default Re: embedded formula

    Seeing:

    "You've entered too many arguments for this function."

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,008

    Default Re: embedded formula

    Hi

    Try:

    =if(b99="","",VLOOKUP($B99,Acct_budget_lookup,11,FALSE))
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    Board Regular
    Join Date
    May 2009
    Posts
    58

    Default Re: embedded formula

    That worked awesome - thank you!

    Trying to get tricky... and probably not a good idea but is there any way to add the following to the formula:

    1. Would like to be able to look for the what is in B99 in more than 1 defined range. Is this possible.

    meaning, look for this "xxx" in defined range "acct-budget1", also in "acct-budget2", also in acct-budget3) and sum the numbers that you find?

    2. If you can't find "B99" (or "xxx") in the defined range (or in any of the defined ranges), return a blank (instead of #N/A).

    I'm way out of my comfortage area now and appreciate any and all help! Thanks!!!

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,008

    Default Re: embedded formula

    This is not clear to me

    1.

    ... look for this "xxx" in defined range "acct-budget1", also in "acct-budget2", also in acct-budget3) and sum the numbers that you find?
    I'm looking for "xxx", what numbers are you talking about?

    2.

    If you can't find "B99" (or "xxx") in the defined range (or in any of the defined ranges), return a blank (instead of #N/A).
    So if "xxx" is not found for ex. in the second range the result is "". Is this correct?

    3.

    What kind of ranges do the acct-budget1/2/3 refer to? Rectangular ranges, vertical vector ranges?

    Please clarify
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    Board Regular
    Join Date
    May 2009
    Posts
    58

    Default Re: embedded formula

    Let me try to explain better...

    I am working a budget. Each project has it's own spreadsheet (tab). I have another where I am looking to basically summarize.

    My defined range goes from B37:AW131; each column signifies monthly data (January hours, cost, Revenue, followed by February data, etc.)

    So I would want a formula that says find (on the project sheets) the account listed in column B on this sheet (summary), and bring me back the number in column {11}. Then I would have another for February that may equate to column {15}, etc.

    Don't know if this helps clarify what I am trying to do?

    Thanks.

  8. #8
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,008

    Default Re: embedded formula

    I'm not yet sure that I understand.
    This is an example of what I understood:

    - you have 2 projects,

    . 1 in worksheet Prj1 in range ='Prj1'!$B$37:$AW$131
    . 1 in worksheet Prj2 in range ='Prj2'!$D$27:$AY$121

    - In the Summary sheet you want to add the values in column 11 of those ranges when column 1 is equal to the value of Summary!B99



    ABCD
    97
    98 Result
    99 a20
    100
    [Book1]Summary
    ABCKLM
    36
    37 a 1
    38 b 2
    39 c 3
    40 a 4
    41 b 5
    42 c 6
    43 a 7
    44 b 8
    45 c 9
    46
    [Book1]Prj1
    CDEMNO
    26
    27 a 1
    28 b 2
    29 c 3
    30 c 4
    31 c 5
    32 c 6
    33 a 7
    34 b 8
    35 c 9
    36
    [Book1]prj2




    AddrFormula
    [Book1]Summary
    C99=SUMIF(INDEX(acct_budget1,0,1),B99,INDEX(acct_budget1,0,11)) +SUMIF(INDEX(acct_budget2,0,1),B99,INDEX(acct_budget2,0,11))



    Names
    NameRefers toWorkbook
    acct_budget1='Prj1'!$B$37:$AW$131Book1
    acct_budget2='prj2'!$D$27:$AY$121Book1
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  9. #9
    Board Regular
    Join Date
    May 2009
    Posts
    58

    Default Re: embedded formula

    This is awesome and exactly what I need!! Thank you so much!!

    To take it a step further, what if I add data to Summary a99 that I want to be a criteria also? Meaning, I want it to sum the defined ranges if the first column =a99 and the second column = b99? Is my only way to concatenate these out of sight?

    Thank you so very much!! I do apologize for my delay in thanks - I was pulled off this for something else for a while and just tried it tonight.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com