How to search below a certain row

[Randy Williams]

There are actually two questions, but help would be
appreciated on either one.
1) I am searching for a code in col. A, which is unique.
How can may I use a text-string search so that I can
minimize code writing. The word is 296OLFM00, a company
code (which happens to be in row 164, let's say). What
command will allow me to search for just "FM", for
example, and go straight to row 164, Col. A?
2) Once the text search takes me to row 164, then I want
Excel to go to the next row (165) and look in col B. From
one to five cost descriptions may randomly appear in
succeeding rows: CICS, 3490, CPU, DAMS, and TAPE.
If only one of those is present, such as CICS, then it is
found in row 165. If all are present, then CICS is found
in row 165, 3490 in 166, DAMS in 167, etc. By the way,
Col. A is blank for each one or more of those cost rows.
Finally, I want to sum (from col. D) the cost amount in
each row that applies to "FM".
A change in Col A causes a new set of costs to appear:
296OLIS, for example. Then the same five cost descriptions
start over, but for a different company, which I don't
want added in.

This is a little complicated. But I sure would appreciate
a little help from a highly talented, Excel wizard. By
the way, I am very comfortable using SUMPRODUCT. Thanks in
advance.

 

[Myrna Larson]

You will make your life and your formulas easier if you eliminate the blanks
in column A. To do that quickly, select column A. Then Edit/Goto, click on
Special, and select blanks. Let's say you have headers in row 1 and 1st
company code in row 2. The first blank should be in A3, and A3 will be the
active cell. Type this formula =A2 and press CTRL+ENTER to put it in all the
blank cells (with the row references adjusted appropriately)

Now you can use ordinary SUMIF formula to get totals for each different
company, using the full company code.

 

[Randy Williams]

Thank you. I will try it.

-----Original Message-----
You will make your life and your formulas easier if you eliminate the blanks
in column A. To do that quickly, select column A. Then Edit/Goto, click on
Special, and select blanks. Let's say you have headers in row 1 and 1st
company code in row 2. The first blank should be in A3, and A3 will be the
active cell. Type this formula =A2 and press CTRL+ENTER to put it in all the
blank cells (with the row references adjusted appropriately)

Now you can use ordinary SUMIF formula to get totals for each different
company, using the full company code.



.