Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Find formula (Excel 97 SR2)

    I have been trying to develop a formula that checks two ranges on another sheet to determine if a certain value is there. If it is found in first range, formula should return a positive 1. If it is found in second range it should return a negative 1. If the vaule is not found in either range, it can return a " " (space). I would also be open to a VBA solution as it would reduce the number of formulas in the workbook. I cut the workbook way back to make it small enough to send. The workbook has better description of what I am trying to do. Any help would be appreciated.
    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Find formula (Excel 97 SR2)

    I am sorry, I have looked at your workbook and I can not figure out what you are trying to do. Your description talks in terms of cities, meters, flows, etc., and those mean absolutely nothing to me. Could you give us a description of what you want to do in terms of worksheets and cells? The formulas in your workbook are no help.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Find formula (Excel 97 SR2)

    Legare,
    On the MeterFormulaChecks sheet, the cities are named across the top and the meters are in the rows. I want to populate the cells showing which meters aer used by which cities. Each city has its own sheet (three in the sample). The meters sheet shows all the meters and what the flow is by month. The city sheet has an area for entering the Meter names used as adds (row 10 pale blue) and for minuses it is row 11 (tan).
    The MeterFormulaChecks sheet is a summary that shows where the meters are used. If a meter is used as an add for a city, the summary sheet will have a 1 (positive one) in the city column that is on the row that the meter is named. If the meter is used as a minus for a city the number on the MeterFormulaChecks sheet would be -1 (negative one). This is all about the MeterFormulaChecks sheet.
    Thanks for your time and I am sorry I was not clear. I hope this helps.

    chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with Find formula (Excel 97 SR2)

    In H129 you can enter:

    =ISNUMBER(MATCH($A129,INDIRECT("'"&H$2&"'!D10:H10" ),0))*1+ISNUMBER(MATCH($A129,INDIRECT("'"&H$2&"'!D 11:H11"),0))*-1

    And this can be copied throughout the range.

    It will give a 1 found in row D10:H10, a -1 ( found in D11:H11) or a zero (0) if not not found.

    If you want to display nothing for not found, format the cells:
    0;-0;

    Steve

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Find formula (Excel 97 SR2)

    Steve,

    Thanks for the formula. I will give it a shot.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Find formula (Excel 97 SR2)

    Steve,
    It worked great! Now I will tweak it to cover the "long form" version where the meters can be entered out to column N.
    Thanks again.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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