Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    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. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
  •