• Build Cell Range in Formula bar

    Author
    Topic
    #472890

    I think I am having a brain fade at the moment and need to solve this problem quickly 🙂

    I have a long formula in a cell on a spreadsheet which, for each row, multiplies a value by a rate found in another table. The rate is looked up based on a category in the original range :

    =IF(H3 “”,SUMPRODUCT(H6:H101*LOOKUP($E$6:$E$101,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,MATCH(H3,Rates!$C$3:$M$3,0)))),””)

    The problem is that the sumproduct ranges are not always fixed i.e. H6:H101 and E6:E101 are not always fixed – it depends how many lines of data the user fills in as input data. It could be H6:500 and E6:E500. My formula doesn’t handle the fact that the range is variable.

    By my reckoning, I could either:

    1) Update the formula so it always does a sumproduct on the maximum range H6:H500 and E6:E500, however the lookup currently only works if the category in column E is not blank. If the value in column E is blank, the formula returns a #N/A. So if the user only inputs 101 rows, I need to change the formula so it ignores from 102 to 500 where column E is blank.

    2) Dynamically build the sumproduct range in the formula using VB or some other method. I have already stored the row number of the last row that the user input in a cell, i.e. if the user input 101 rows, the cell contains the value 101, or if he input 500 rows, it contains the value 500. Perhaps there is some way I can modify the formula to avoid hardcoding the ranges.

    Bit difficult to explain.

    Cheers,
    Dom

    Viewing 0 reply threads
    Author
    Replies
    Viewing 0 reply threads
    Reply To: Reply #1254209 in Build Cell Range in Formula bar

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




    Cancel