Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Help with vlookup-type formula, but vlookup won't work as fa

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have spreadsheet data similar to below:

    Spreadsheet tab name TEAMS

    Col A Col B Col C
    TOM RED 0
    JOE RED 100
    BOB WHITE 200
    PAT WHITE 300
    DAN BLUE 400
    KEN BLUE 500

    I am hoping for a formula to place in a cell that will be located within another spreadsheet tab in the same file, that will look at Column B above, and where RED or BLUE only (but not WHITE or other), will return the value in Column C. Also, if the value is zero, as in the first row above, I would like it to return a numerical 0, not #N/A if possible.

    Any help will be most appreciated.

    Thank you,

    vantilian





  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-04 08:32, vantilian wrote:
    I have spreadsheet data similar to below:

    Spreadsheet tab name TEAMS

    Col A Col B Col C
    TOM RED 0
    JOE RED 100
    BOB WHITE 200
    PAT WHITE 300
    DAN BLUE 400
    KEN BLUE 500

    I am hoping for a formula to place in a cell that will be located within another spreadsheet tab in the same file, that will look at Column B above, and where RED or BLUE only (but not WHITE or other), will return the value in Column C. Also, if the value is zero, as in the first row above, I would like it to return a numerical 0, not #N/A if possible.

    Any help will be most appreciated.

    Thank you,

    vantilian




    Which value of BLUE would you want returned in your example above?

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm sorry, the more I thought about it I wasn't very clear. Actually I would want both values of BLUE. Let me express it another way. I'm hoping for a formula that will look at the TEAM spreadsheet, then for certain words in Column B (RED, BLUE) and sum their values of Column C. So, formula would return the sum of Column C value for RED, BLUE, or whatever other words were designated to sum. In this example the sum would be 1,000.

    I really appreciate you taking the time to review this.

    Thanks,

    Russell

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not use an Advanced Filter to return record based on column B?

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to set it up so that when the spreasheet updates the formula simply recalcs the Column C value sums for certain Names in Column B. Trying to eliminate any manual intervention following the spreadsheet update.

    Thanks for asking and taking the time to review this.

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually I would want both values of BLUE. Let me express it another way. I'm hoping for a formula that will look at the TEAM spreadsheet, then for certain words in Column B (RED, BLUE) and sum their values of Column C. So, formula would return the sum of Column C value for RED, BLUE, or whatever other words were designated to sum. In this example the sum would be 1,000.
    Try using an array formula with the following type syntax:
    =SUM(IF(OR(B1:B4="BLUE";B1:B4="RED");C1:C4))
    just remember to press Ctrl-Shift-Enter
    You can also replace "Blue" and "Red" with references to cells...

    Hope this works for you.

    J

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    J, thank you the answer. I tried using the formula you suggested, but I get the general "formula you typed contains an error". I combed over it several times to make sure I typed correctly and hit CTRL-SHIFT-ENTER for array. Again, thanks your response.

    Russell

  8. #8
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another option would be to use MS Query. Choose Excel Files for your Database source and direct MS query to the Worksheet where the data is located. The query can be in the same workbook or another one. Once you set up the query parameters, it can be set to run automatically.

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've got a Summary Report spreadsheet that the TEAM (example above) spreadsheet update drives. It has other data groupings and values reported. In this case, I am trying to add one additional line to the Summary Report that would be, for example, "Color Group A" (Red & Blue) with the sum values of Column C for Red & Blue pulled via the formula in question placed on the Summary Report. Ultimate goal to have spreadsheet update drive that Summary line.

    Thank you for the good suggestion, and I will consider it for other uses as well.

    Russell

Some videos you may like

User Tag List

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
  •