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
