Formula stops working with named range

[Fishboy]

In Excel 2010 I have recently started swapping out referenced ranges for named ranges to account for additional data being added without formulas breaking, and on the whole this has been a success. I do however have a single formula that refuses to play ball using the named range, despite working fine using standard alpha-numeric references.

Here's what I have that works:

{=MAX(IF('Raw Purchase Data'!K2:K2102<>"Merchant",'Raw Purchase Data'!L2:L2102))}

Here are the definitions of the 2 named ranges above:

Range containing purely text values
Name: Purchase_SellerNameShort
RefersTo: ='Raw Purchase Data'!$K$2:INDEX('Raw Purchase Data'!$K:$K,MATCH("zzzz",'Raw Purchase Data'!$K:$K))

Range containing purely numeric values (except header)
Name: Purchase_TotalValue
RefersTo: ='Raw Purchase Data'!$L$2:INDEX('Raw Purchase Data'!$L:$L, MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L), 1)

Here's what I tried amending the formula to using the names ranges:

{=MAX(IF(Purchase_SellerNameShort<>"Merchant",Purchase_TotalValue))}

The working formula returns the desired result of a the highest combined spend with a seller that isn't called "Merchant".

The non-working formula returns a result of #N/A

Can anyone think of a reason this would be? Are there problems with the dynamic ranges I have created that would cause it? Have I written the MAX / IF statement incorrectly?

Any help would be greatly appreciated.

 

[GerryZ]

Hello!
I like this question but I don't understand all of the issue It was nice to see a spreadsheet sample instead to use 1000s of words and formula
Can you post a screenshot with just few rows and colunms with the expected outcome so we can understand better?
I suggest you to post a nice HTML screen-shot with Mr.Excel HTML Maker, if you don’t know how to istall and how to use it watch the video linked in my signature
help us to help you
Thank you in advance!

 

[ghrain22]

It doesn't appear that your named ranges are correct references. I think this is what you're trying to do:
=OFFSET(Sheet1!$K$2,0,0,MATCH("zzzz",Sheet1!$K:$K))

Another good way to do this is with counta's instead of match's

 

[Jonmo1]

The most likely cause is that the 2 named ranges are of different demensions.
Bot ranges must contain the same number of rows.

So this
MATCH("zzzz",'Raw Purchase Data'!$K:$K)
Probably has a different result than this
MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L)

You should use the same method of finding the end row in both formulas.
Either MATCH("zzzz",'Raw Purchase Data'!$K:$K) or MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L)

Personally, I'd go with MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L)

So try

Name: End_Row
=MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L)

Name: Purchase_SellerNameShort
RefersTo: ='Raw Purchase Data'!$K$2:INDEX('Raw Purchase Data'!$K:$K,End_Row)

Name: Purchase_TotalValue
RefersTo: ='Raw Purchase Data'!$L$2:INDEX('Raw Purchase Data'!$L:$L,End_Row)

 

[Fishboy]

The most likely cause is that the 2 named ranges are of different demensions.
Bot ranges must contain the same number of rows.

So this
MATCH("zzzz",'Raw Purchase Data'!$K:$K)
Probably has a different result than this
MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L)

You should use the same method of finding the end row in both formulas.
Either MATCH("zzzz",'Raw Purchase Data'!$K:$K) or MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L)

Personally, I'd go with MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L)

So try

Name: End_Row
=MATCH(9.99E+307, 'Raw Purchase Data'!$L:$L)

Name: Purchase_SellerNameShort
RefersTo: ='Raw Purchase Data'!$K$2:INDEX('Raw Purchase Data'!$K:$K,End_Row)

Name: Purchase_TotalValue
RefersTo: ='Raw Purchase Data'!$L$2:INDEX('Raw Purchase Data'!$L:$L,End_Row)

Hi Jonmo, thanks for your reply.

I had originally been advised to set the ranges up in this way as I had been told that "zzzz" worked better for text based ranges, and 9.99E+307 worked better for numeric based ranges. If this is not the case I am more than happy to amend my ranges to something more suitable.

With this in mind however, these ranges are technically of slightly different lengths as follows:

The data in Purchase_SellerNameShort is created by filtering the thousands of entries from column F (Purchase_SellerNameFull), and outputting only the unique names to column K (basically removing the duplicates of each seller).

The data in Purchase_TotalValue is created by a formula in column L which is then filled down to the last row by a macro.

=SUMIF(Purchase_SellerNameFull,K2,Purchase_SaleValue)/10000

The problem here is that column L (and thus Purchase_TotalValue) goes all the way to the last row of data in the table, where as column K (and thus Purchase_SellerNameShort) does not go as far down the page.

I think the easiest way to resolve this would be to make the macro that copies the formula to column L stops at the last row of data in column K rather than the last row of data in column A, which is what is currently happening.

 

[Fishboy]

This is the part of my massive macro that is adding and filling down the formula in column L:

Range("L2").Formula = "=SUMIF(Purchase_SellerNameFull,K2,Purchase_SaleValue)/10000"
Range("L2:L" & LastRow).FillDown
Range("L2:L" & LastRow).Select
Selection.NumberFormat = "#,##0.00"

Is there a clever way to tell this macro to stop filling down at the last populated row of column K rather than the last populated row of the entire sheet?

 

[Jonmo1]

I had been told that "zzzz" worked better for text based ranges, and 9.99E+307 worked better for numeric based ranges.

That is true, Though 'better' isn't quite the right way to describe the difference..
Actually "zzzz" doesn't work at all for numeric ranges, and 9.99E+307 doesn't work at all for text ranges...

Anyway, this is irrelevant to the issue here.

The issue here is
For your array formula to work, both ranges MUST contain the same number of rows.
Period.

Since it is known that both columns do not contain data all the way to the end,
then as your ranges are currently created, they will have a different number of rows in each.

It doesn't really matter which method is used to determine the ending row, "zzzz" or 9.99E+307
Just choose the one that will find the value that is furthest down.

And given this

column L (and thus Purchase_TotalValue) goes all the way to the last row of data in the table, where as column K (and thus Purchase_SellerNameShort) does not go as far down the page.

Then Column L should be the one used.
Since Column L is the numeric column, use the 9.99E+307


Hope that helps.

 

[Fishboy]

Awesome! Not only does this work, but I understand why it wasn't working, which is even better!

Thanks Jonmo1!

 

[Jonmo1]

You're welcome