Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Search thru file and calc formulas (Excel xp)

    I have the spreadsheet attached. I need to search thru a file that has 50 of the tables attached (the attached only has 2). The cells that are red are the ones i need to find for all 50 tables. So in the first table, i need to look thru B8 to B17 and find the first cell that is <20. Then I need to put the value of that cell in H5 and the dollar amt it corresponds to in H4. I need to do cars 1 thru car 6 and then move on to the next table. Each "table" is separated by #page so i can search for page.

    Could i get some help with the code...

    search for "#page"
    car1 code to find first value <20, get corresponding dollar amount in COL A
    car1 code to find first value >70, get corresponding dollar amount in COL A
    go to car2
    ...


    Thank you for the help.
    Attached Files Attached Files

  2. #2
    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: Search thru file and calc formulas (Excel xp)

    Instead of a macro, the following formulas, give what you want.
    In H4 (with "<") you could enter something like:
    <pre>=INDEX($A8:$A17,MATCH(20,B8:B17,-1)+1)
    </pre>

    In H5:
    <pre>=INDEX(B8:B17,MATCH(20,B8:B17,-1)+1)
    </pre>


    For the ">" [This will actually find ">="] in I4/I5
    <pre>=INDEX($A8:$A17,MATCH(70,B8:B17,-1))
    =INDEX(B8:B17,MATCH(70,B8:B17,-1))
    </pre>


    You could put the 20 and 70 in a cell and reference them if desired, and these could be copied at the top of each of the tables.

    Steve

Posting Permissions

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