Replace Index and Sumproduct Formula with VBA code

  • Hey everyone!


    I originally received help a long time ago here when I had a project!
    Working on that same project, buy my experience in excel has grown some and I've learned a little VBA and want to improve the formula I've been relying on to keep my status board updated in excel.



    The formula looks at the A/C Tail number. For Example in Cell D2, the formula is looking at A/C 92 in Cell B2, and Engine Position 1 (AL5), then looks on the Data sheet, to see what line that matches to and returns the row number. With the row number I can use index to get the letter code related to that engine and aircraft.


    The problem with this formula is i constantly have to update the ranges for how many rows it scans over for data. As we receive more samples and the data page starts to become larger the computer cannot handle a unlimited range of scanning the rows each time.


    My goal is to start VBA code that updates when the workbook is saved. I barley understand how this formula works so I was hoping to get some help with the VBA code, at the very least pointed in the direction I need to work towards

  • Hi,


    When you say


    Quote

    For Example in Cell D2, the formula is looking at A/C 92 in Cell B2, and Engine Position 1 (AL5)


    In your Sheet ' Status Board ' ... believe you are looking for a fix for cell D5 :wink:


    Attached is your test file with a proposal in order to forget the total number of rows in Sheet ' Data ' ...


    Once everything is automatic ... do you still need a macro to completely get rid of the formula ...?


    Hope this will help

  • [USER="31712"]Carim[/USER]


    I'm looking at your formula in cell A5.


    I have a couple of questions. It's finding the last row on the data tab - 1. So if the data tab has 119 rows and A5 says 118. But the formula in cell D7 still works even if its looking for the value the data tab on row 119. Confused on how that is working. I'm glad it is! just trying to understand!


    With this solution the only value I'll have to change is in A5?
    Change the C2-C10000 to match my ranges in the future?


    and no I don't think they'll be a need for any macro this is way easier to update one cell then all them!

  • Glad formula is helping you out ...


    To explain the -1 ... you have to know how the Offset() function operates from its starting cell reference ....


    https://support.office.com/en-…79-4B9B-A14E-B4D906D11B66


    Should your Daily Input sheet exceed 10'000 records ... you would indeed need to adjust the last row formula accordingly ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • [USER="31712"]Carim[/USER]


    To complicate things a bit more, If I took the average of all the last entries and -2500


    =AVERAGE(D5:D30,G5:G30,L5:L31,O5:O31)-2500
    Results: 10182 in Cell L4


    Can I use offset to replace the low end number in the formula


    =SUMPRODUCT(MAX((ROW(Data!$C$11000:OFFSET(Data!$C$10000,List!$L$2,0)))*(Data!$C$11000:OFFSET(Data!$C$11000,List!$L$2,0)=$J5)*(Data!$D$11000:OFFSET(Data!$C$2,List!$L$2,0)=AA$5)))


    So use offset on both sides of the formula basically, one the left side its taking a average and subtracting 2500 rows from that for the low end search range, and on the high end search range its just finding the last row on the data tab

  • Sorry ... cannot understand your last message ...


    May be you could attach your latest file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Indeed even a very small file ... which correctly replicates your problem ... will be enough ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!