Thread: Build Cell Range in Formula bar
2010-11-08, 04:00 #1
- Join Date
- Oct 2007
- Thanked 0 Times in 0 Posts
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.
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2010-11-08, 06:49 #2
- Join Date
- Feb 2002
- Portland, Oregon, USA
- Thanked 3 Times in 3 Posts