# Thread: Build Cell Range in Formula bar

1. 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

2. You could use a dynamic range that self adjusts...
Example... http://www.contextures.com/xlNames01.html#Dynamic
''---
Jim Cone
Portland, Oregon USA
30 + ways to sort in excel

#### Posting Permissions

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