Excel 2003 named range in formula

[pdeman]

Hello

I have the following two formulas in a Microsoft Excel 2003 spreadsheet:

=IF(OR(B3=0,C3=0),"",SUMPRODUCT(--(LEFT(‘Places won'!$B$2:$B$574,LEN(B3)-5)=LEFT(B3,LEN(B3)-5)),--(RIGHT(‘Places won'!$B$2:$B$574,6)>=RIGHT(B3,6)),--((‘Places won'!$C$2:$C$574)=5000),--(RIGHT(‘Places won'!$B$2:$B$574,6)<=RIGHT($C3,6)),--((‘Places won'!$C$2:$C$574)=5000)))

=IF((SUMIF((‘Places won'!A2:A574),E1&TEXT(A6,"yy"),(‘Places won'!C2:C574)))>0,SUMIF((‘Places won'!A2:A574),E1&TEXT(A6,"yy"),(‘Places won'!C2:C574)),"")

I need to replace each reference of:
‘Places won'!C2:C574
To my named range which is “database”

I tried the following with and without apostrophes (‘) and brackets () but it doesn’t work?

=IF(OR(B3=0,C3=0),"",SUMPRODUCT(--(LEFT(‘Places won'!$B$2:$B$574,LEN(B3)-5)=LEFT(B3,LEN(B3)-5)),--(RIGHT(‘Places won'!$B$2:$B$574,6)>=RIGHT(B3,6)),--((database)=5000),--(RIGHT(‘Places won'!$B$2:$B$574,6)<=RIGHT($C3,6)),--((database)=5000)))

=IF((SUMIF((‘Places won'!A2:A574),E1&TEXT(A6,"yy"),(database)))>0,SUMIF((database),E1&TEXT(A6,"yy"),(database)),"")

Can any one help with the right code?

 

[SkipVought]

Hi,

What does reference database refer to?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

sorry, I see the answer.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Please post a portion of your columns B & C, so we can test.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pdeman]

Hi Skip,

Here is a sample:

B C
88GP713381 99
88GP773782 99
88GP713383 99
236PN816990 299
236PN816881 99
33KL702537 199
88GP713223 399
33KL708537 399
33KL602538 99
33KL705439 99

 

[Gavona]

A namedrange database is generated by excel under some circumstances, for example when using advanced filters. If two ranges in the workbook use the same name (names can be sheet specific) then unexpected results can result. So as a precaution I would try assigning a different name to your database, maybe myDatabase.

All ranges (arrays) in sumproduct must be the same size. Is database 573 rows by 1 column?

Gavin

 

[SkipVought]

If you have Range Names, you can, while you are editing your expression, hit the F3 key, and Excel will pop up a list of named ranges to select from.

As a matter of practice, I use Table/List HEADINGS to generate column range names, by 1) selecting all the table/list 2) Insert > Name > Create -- Create names in TOP row CAVEAT: SPACES are replaced by UNDERSCORE in any Range Name.

So, in your example, if this were your table...
[tt]
My Code My Value
88GP713381 99
88GP773782 5000
88GP713383 99
236PN816990 299
236PN816881 99
33KL702537 199
88GP713223 399
33KL708537 399
33KL602538 99
33KL705439 99
[/tt]
the formula...
[tt]
=IF(OR(My_Code=0,My_Value=0),"",SUMPRODUCT((LEFT(My_Code,LEN(B2)-5)=LEFT(B2,LEN(B2)-5))*(RIGHT(My_Code,6)>=RIGHT(B2,6))*((My_Value)=5000)))
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pdeman]

Thank you

I just wanted to replace each reference of:
'Places won'!C2:C574
With my named range which can be anything lets say its "MyData"
The simple object of the exercise was to be able to add rows to that data and becouse it was a named range the expression would see it. Whereas now it only sees upto row 574.

If you look back at my original thread you can see the unchanged expression and below that you can see I changed the portion of the expression that looks at the number of rows with my named range which I have now called "MyData". But unfortunately it does not work.

 

[SkipVought]

It DOES work, if you have named your ranges correctly (ie consistent with all other ranges)

I do not understand your problem.

EXACTLY what do you mean by, "it does not work?"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pdeman]

Hi Skip

Apologies. Yes it does work.

This is the second challenge...
I have the following data with this formula =IF(D1=0,"",(B1/100*D1)*(1-E1%)) in column F.
In this portion of the expression ...(B1/100*D1)... I need D1 to increment over the twelve months of a year and not just *D1?

Column D is a full years percent value.
So in the following example with the formula above copied through all the rows the total in column F is only right in December. This of course is the problem and why I need to multiply it incrementally for each month and not just the whole D1 value.

So in January it would be 1 twelfth of D in February 2 twelfths and so on.

Column D figure needs to stay as it is so must all be done in Column F formula.
Sorry for the long explanation but it’s not easy to explain.

A B C D E F
1 2001 10,000 3.50 3.75 20.00 £300.00
2 2002 15,000 2.00 3.50 20.00 £420.00
3 2003 15,000 1.67 3.50 20.00 £420.00
4 2004 15,000 2.33 3.25 20.00 £390.00
5 2005 15,000 3.33 3.25 20.00 £390.00

 

[SkipVought]

"So in January it would be 1 twelfth of D in February 2 twelfths and so on."

What, in your data, indicates any kind of date? You seem to have data from different YEARS (column A), or am I missing something?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, if this is a different topic, which it seems, please start another thread.

One thread per topic.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pdeman]

Thank you Skip will do