# Thread: Help with Find formula (Excel 97 SR2)

1. ## 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

2. ## 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.

3. ## 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

4. ## 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. ## Re: Help with Find formula (Excel 97 SR2)

Steve,

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

Chuck

6. ## 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

#### Posting Permissions

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