Most valuable professional
  • Hot Topics

Excel Formula that has a dynamic ending cell reference

Hi all, I have run into a number of problems with my Excel 2010 spreadsheet, but they all basically result to the same thing...Ranges in formulas when new data is added. For example I have the following formula: =INDEX('Raw Sales Data'!$J$2:$J$5000,MATCH(Dashboard!J9,'Raw Sales Data'!$K$2:$K$5000,0)) It basically is checking 'Raw Sales Data' sheet cells K2:K5000 (Spend Amount), finding the value that matches 'Dashboard' sheet cell J9 (Spend Amount), then returning the corresponding adjacent value from 'Raw Sales Data' sheet cells J2:J5000 (Customer) This is all good and works fine. But... If more rows are added to Raw Sales Data, the range expands beyond J5000. Is there a formula friendly equivalent to the VBA find last row function? In a nutshell, if 500 more rows of data are added I don't want to have to go amend all my formulas from J5000 to J5500. I also am trying to avoid just putting an exaggerated value like J10000 as some formulas really get funny about all of the blank data between J5000 and J10000 Last edited by Fishboy; Apr 1st, 2015 at 10:19 AM. Reason: Added to final sentence

This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of April 1, 2015.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel