Formula that has a dynamic ending cell reference

[Fishboy]

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

 

[strategygamer]

Not sure if this contradicts the last statement, but why not just reference the entire column?
=INDEX('Raw Sales Data'!$J:$J,MATCH('Dashboard'!J9,'Raw Sales Data'!$K:$K,0))

 

[mikerickson]

Or you can use a Dynamic Named Range

Name: dataRange
RefersTo: ='Raw Sales Data'!$J$2:INDEX('Raw Sales Data"!$J;$J, MATCH("zzzz", 'Raw Sales Data"!$J;$J), 1)

with the formula

=INDEX(dataRange,MATCH('Dashboard'!J9,'Raw Sales Data'!$K$2:$K$5000,0))


The MATCH("zzzz", 'Raw Sales Data"!$J;$J) is the "last row" that you were asking for

 

[Fishboy]

Hi Strategygamer, thanks for the response.

I didn't mention it in my original post, but again ideally I would like to avoid referencing an entire column. There are 2 reasons for this:

1. Referencing the entire column slows the whole process down quite considerably and can cause the spreadsheet to become sluggish and unresponsive.
2. Some formulas just simply stop working if there are blank cells being referenced.

I'm sure there must be a formula friendly way to express J2:J"last non blank cell in range", I just cannot find one.

 

[Fishboy]

Or you can use a Dynamic Named Range

Name: dataRange
RefersTo: ='Raw Sales Data'!$J$2:INDEX('Raw Sales Data"!$J;$J, MATCH("zzzz", 'Raw Sales Data"!$J;$J), 1)

with the formula

=INDEX(dataRange,MATCH('Dashboard'!J9,'Raw Sales Data'!$K$2:$K$5000,0))


The MATCH("zzzz", 'Raw Sales Data"!$J;$J) is the "last row" that you were asking for

Hi Mike,

Thanks for taking the time to look at this, but I am getting an error when trying to create the dynamic range. It is flagging an error here:

='Raw Sales Data'!$J$2:INDEX('Raw

And then will not complete creating the named range

 

[Fishboy]

I think I have finally got it sorted now using dynamic named ranges (thanks for the tip Mike!) using the following

Name: dataRange
RefersTo: =OFFSET('Raw Sales Data'!$E$1,0,0,COUNTA('Raw Sales Data'!$E:$E),1)

 

[mikerickson]

If you replace my mistaken double quote with an apostrophy in the errored sheet name, my formula will return the same. The INDEX is not volatile like OFFSET.

 

[Fishboy]

Sorry Rick, even with the typo rectified this still doesn't work for me. It still falls over at range creation with a "The formula you have typed contains an error" and flags the issue at this point:

='Raw Sales Data'!$J$2:INDEX('Raw Sales Data"!$J


[EDIT] Nevermind, there was another typo in the example where it reads ; instead of :

 

[Fishboy]

Ok, so with the typos corrected I can successfully create the named range. I have tried to amend and re-apply the corrected RefersTo: so other ranges can be created and I assumed all I needed to do is change the column letters to match the desired columns and give the range a sensible name. So my..

Name: Sales_CustNameFull
RefersTo: ='Raw Sales Data'!$J$2:INDEX('Raw Sales Data'!$J:$J, MATCH("zzzz", 'Raw Sales Data'!$J:$J), 1)

became

Name: Sales_TotalValue
RefersTo: ='Raw Sales Data'!$K$2:INDEX('Raw Sales Data'!$K:$K, MATCH("zzzz", 'Raw Sales Data'!$K:$K), 1)

And so on, and this all seemed to be fine until I started replacing the cell references in my formulas with the named ranges at which point the results being returned were no longer correct. For example this is the original working formula that gives the correct result:

=INDEX('Raw Sales Data'!$J$2:$J$5000,MATCH(J9,'Raw Sales Data'!$K$2:$K$5000,0)) correct result

If I change the J data range to Sales_CustNameFull but leave the K data range at the end it still gives the right answer.
=INDEX(Sales_CustNameFull,MATCH(J9,'Raw Sales Data'!$K$2:$K$5000,0)) correct result

If I change the K data range at the end to Sales_TotalValue I get the wrong results.
=INDEX(Sales_CustNameFull,MATCH(J9,Sales_TotalValue,0)) incorrect result

So I tested this out by only changing the K data range by itself, and this also gave the wrong results.
=INDEX('Raw Sales Data'!$J$2:$J$5000,MATCH(J9,Sales_TotalValue,0)) incorrect result

So the problem is obviously with the second named range, but I cant see why, when all that has changed is the column letter.

If it comes to it I will try and provide a copy of my workbook so it can be looked at "in situ", but it is quite a lot of data that I cant really cut down without destroying the essence of the spreadsheet.

 

[XOR LX]

Hi.

What are the results of both:

=MATCH(J9,'Raw Sales Data'!$K$2:$K$5000,0)

and:

=MATCH(J9,Sales_TotalValue,0)

?

Regards