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


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

This thread is current as of April 01, 2015.


For more resources for Microsoft Excel