Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Thanked 1 Time 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.


  2. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Portland, Oregon, USA
    Thanked 3 Times in 3 Posts
    You could use a dynamic range that self adjusts...
    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