row number question in an array

[Guest]

Hi there,

I have a forumula

=SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
INJ'!$H$2:$H$55="30000001PC",1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))

My problem is: because it is an array the row number 55 needs be the last
line (ie there cant be any blanks after the last bit of data). This formula
references a query so the number of rows changes daily, one day it might be
70 next 55. How can I get this to reference the exact number of rows in
"scheduled inj "worksheets.

I have a row count set up but need this formula to reference it.

cheers

SD

 

[Guest]

you could use INDIRECT

=INDIRECT("A"&C1)

where C1 contains the row number.

you could also look at dynamic named ranges

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

 

[Biff]

Hi!

My problem is: because it is an array the row number 55 needs be the last
line (ie there cant be any blanks after the last bit of data).

I don't understand your reasoning, but, try this:

A1 = row count

Normally entered, not an array:

=SUMPRODUCT(--(INDIRECT("'SCHEDULED
INJ'!AL2:AL"&A1)=D3),--(INDIRECT("'SCHEDULED
INJ'!H2:H"&A1)="30000001PC"),INDIRECT("'SCHEDULED INJ'!AM2:AM"&A1))

Biff

 

[Guest]

Thanks very much...can i ask what the -- means next to indirect???

 

[Biff]

Hi!

Each cell in the arrays:

AL2:ALn=D3
H2:Hn=30000001PC

will return either TRUE or FALSE.

The "--" converts those to either 1 for TRUE or 0 for FALSE.

Then you end up having all 3 arrays multiplied together then summed for the
final result:

1*0*10=0
1*1*20=20
0*1*10=0
0*0*20=0

Result = 20

Biff

 

[Guest]

I checked Chip's website and have used his formula for creating a dynamic
named range. But I can't find any way to do this: when I used a dynamic
named range in a data validation, I would like the items to appear in
alphabetical order (even though the items in the list are not). Is there any
version of the dynamic named range formula that would alphabetize the list?
Thanks in advance.

 

[Gord Dibben]

Debra Dalgleish has instructions for autosorting a drop-down list.

http://www.contextures.on.ca/xlDataVal13.html

Download the sample file for a working example.


Gord Dibben MS Excel MVP

 

[Guest]

Sorry to appear helpless, but I don't see anything on her site or in the
zipped file that addresses alphabeticizing the items in the drop-down. But
I'm encouraged to learn it might be possible!

 

[Biff]

Well, the idea is that you can't alphabetize the drop down list itself.
You'd have to create a sublist that is alphabetized and then use that list
as the source for the drop down list.

Biff

 

[How can I alphabetize a list of names in]

Hi there,

I have a forumula

=SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
INJ'!$H$2:$H$55="30000001PC",1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))

My problem is: because it is an array the row number 55 needs be the last
line (ie there cant be any blanks after the last bit of data). This formula
references a query so the number of rows changes daily, one day it might be
70 next 55. How can I get this to reference the exact number of rows in
"scheduled inj "worksheets.

I have a row count set up but need this formula to reference it.

cheers

SD

 

[Bob Phillips]

=SUM(IF((OFFSET('SCHEDULED INJ'!$AL$2,,,COUNTA('SCHEDULED
INJ'!$AL:$AL),1)=D3)*(OFFSET('SCHEDULED INJ'!$H$2,,,COUNTA('SCHEDULED
INJ'!$AL:$AL),1)="30000001PC"),(OFFSET('SCHEDULED
INJ'!$AM$2,,,COUNTA('SCHEDULED INJ'!$AL:$AL),1))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"How can I alphabetize a list of names in" <How can I alphabetize a list of
names (e-mail address removed)> wrote in message