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

 

[Fishboy]

Hi.

What are the results of both:

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

and:

=MATCH(J9,Sales_TotalValue,0)

?

Regards

=MATCH(J9,'Raw Sales Data'!$K$2:$K$5000,0) returns a value of 902

=MATCH(J9,Sales_TotalValue,0) returns a value of #N/A

 

[XOR LX]

Thanks. And how is Sales_TotalValue currently defined?

Regards

 

[bosco_yip]

Named ranges, Name: Sales_TotalValue

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

Change to :

RefersTo: ='Raw Sales Data'!$K$2:INDEX('Raw Sales Data'!$K:$K,MATCH(9.99E+307,'Raw Sales Data'!$K:$K))

Reason :

...:INDEX(….MATCH("zzzz",……. >> Refer to text data dynamic range formula

...:INDEX(….MATCH(9.99E+307,……. >> Refer to numeric data dynamic range formula

 

[Fishboy]

Thanks. And how is Sales_TotalValue currently defined?

Regards

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

And to reiterate...

=MATCH(J9,'Raw Sales Data'!$K$2:$K$5000,0) returns a value of 902

=MATCH(J9,Sales_TotalValue,0) returns a value of #N/A

 

[Fishboy]

Named ranges, Name: Sales_TotalValue

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

Change to :

RefersTo: ='Raw Sales Data'!$K$2:INDEX('Raw Sales Data'!$K:$K,MATCH(9.99E+307,'Raw Sales Data'!$K:$K))

Reason :

...:INDEX(….MATCH("zzzz",……. >> Refer to text data dynamic range formula

...:INDEX(….MATCH(9.99E+307,……. >> Refer to numeric data dynamic range formula

Ok, so this works, at least for the formula in my example:

=INDEX(Sales_CustNameFull,MATCH(J9,Sales_TotalValue,0))

Before I spend ages updating ranges again only to find out it does work, am I good to re-use

RefersTo: ='Raw Sales Data'!$K$2:INDEX('Raw Sales Data'!$K:$K,MATCH(9.99E+307,'Raw Sales Data'!$K:$K))

and simply update the column letter as required, or are there other things that will need to be amended also?

 

[XOR LX]

And is bosco_yip's assumption correct, i.e. that column K in Raw Sales Data contains not text but numeric entries?

If not, what result does this formula give:

=MATCH("zzzz", 'Raw Sales Data'!$K:$K)

?

Regards

 

[Fishboy]

Technically speaking, column K are formulas themselves:

=SUMIF('Raw Sales Data'!$E$2:$E$19000,J2,'Raw Sales Data'!$D$2:$D$19000)/10000

But the display values are all numeric beyond the column header.

 

[bosco_yip]

....Ok, so this works, at least for the formula in my example....or are there other things that will need to be amended also?

OK, that is fine,

$K$2:INDEX($K:$K,MATCH("zzzz",$K:$K))

$K$2:INDEX($K:$K, MATCH(9.99E+307,$K:$K))

You can play more to understand these 2 dynamic range formula how to work, by adding text, number or blank cells inside the testing column.

Regards

 

[Fishboy]

OK, that is fine,

$K$2:INDEX($K:$K,MATCH("zzzz",$K:$K))

$K$2:INDEX($K:$K, MATCH(9.99E+307,$K:$K))

You can play more to understand these 2 dynamic range formula how to work, by adding text, number or blank cells inside the testing column.

Regards

Fantastic! What I was doing wrong was thinking either one OR the other of these formulas was the single answer when I need to be using both. The "zzzz" one for the text columns and the 9.99E+307 one for the numeric columns.

Now I have got my head round it everything seems to be working fine.

Thanks Bosco!

 

[bosco_yip]

You are welcome.