software

Hi there,

I have a forumula

=SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
INJ'!$H$2:$H$55=quot;30000001PCquot;,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
quot;scheduled inj quot;worksheets.

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

cheers

SD

you could use INDIRECT

=INDIRECT(quot;Aquot;amp;C1)

where C1 contains the row number.

you could also look at dynamic named ranges

www.cpearson.com/excel/excelF.htm#DynamicRangesquot;SDquot; wrote:

gt; Hi there,
gt;
gt; I have a forumula
gt;
gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt;
gt; My problem is: because it is an array the row number 55 needs be the last
gt; line (ie there cant be any blanks after the last bit of data). This formula
gt; references a query so the number of rows changes daily, one day it might be
gt; 70 next 55. How can I get this to reference the exact number of rows in
gt; quot;scheduled inj quot;worksheets.
gt;
gt; I have a row count set up but need this formula to reference it.
gt;
gt; cheers
gt;
gt; SD

Hi!

gt; My problem is: because it is an array the row number 55 needs be the last
gt; 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(quot;'SCHEDULED
INJ'!AL2:ALquot;amp;A1)=D3),--(INDIRECT(quot;'SCHEDULED
INJ'!H2:Hquot;amp;A1)=quot;30000001PCquot;),INDIRECT(quot;'SCHEDULED INJ'!AM2:AMquot;amp;A1))

Biff

quot;SDquot; gt; wrote in message
...
gt; Hi there,
gt;
gt; I have a forumula
gt;
gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt;
gt; My problem is: because it is an array the row number 55 needs be the last
gt; line (ie there cant be any blanks after the last bit of data). This
gt; formula
gt; references a query so the number of rows changes daily, one day it might
gt; be
gt; 70 next 55. How can I get this to reference the exact number of rows in
gt; quot;scheduled inj quot;worksheets.
gt;
gt; I have a row count set up but need this formula to reference it.
gt;
gt; cheers
gt;
gt; SD
Thanks very much...can i ask what the -- means next to indirect???

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; gt; My problem is: because it is an array the row number 55 needs be the last
gt; gt; line (ie there cant be any blanks after the last bit of data).
gt;
gt; I don't understand your reasoning, but, try this:
gt;
gt; A1 = row count
gt;
gt; Normally entered, not an array:
gt;
gt; =SUMPRODUCT(--(INDIRECT(quot;'SCHEDULED
gt; INJ'!AL2:ALquot;amp;A1)=D3),--(INDIRECT(quot;'SCHEDULED
gt; INJ'!H2:Hquot;amp;A1)=quot;30000001PCquot;),INDIRECT(quot;'SCHEDULED INJ'!AM2:AMquot;amp;A1))
gt;
gt; Biff
gt;
gt; quot;SDquot; gt; wrote in message
gt; ...
gt; gt; Hi there,
gt; gt;
gt; gt; I have a forumula
gt; gt;
gt; gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt; gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt; gt;
gt; gt; My problem is: because it is an array the row number 55 needs be the last
gt; gt; line (ie there cant be any blanks after the last bit of data). This
gt; gt; formula
gt; gt; references a query so the number of rows changes daily, one day it might
gt; gt; be
gt; gt; 70 next 55. How can I get this to reference the exact number of rows in
gt; gt; quot;scheduled inj quot;worksheets.
gt; gt;
gt; gt; I have a row count set up but need this formula to reference it.
gt; gt;
gt; gt; cheers
gt; gt;
gt; gt; SD
gt;
gt;
gt;

Hi!

Each cell in the arrays:

AL2:ALn=D3
H2:Hn=30000001PC

will return either TRUE or FALSE.

The quot;--quot; 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

quot;SDquot; gt; wrote in message
...
gt; Thanks very much...can i ask what the -- means next to indirect???
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; gt; My problem is: because it is an array the row number 55 needs be the
gt;gt; gt; last
gt;gt; gt; line (ie there cant be any blanks after the last bit of data).
gt;gt;
gt;gt; I don't understand your reasoning, but, try this:
gt;gt;
gt;gt; A1 = row count
gt;gt;
gt;gt; Normally entered, not an array:
gt;gt;
gt;gt; =SUMPRODUCT(--(INDIRECT(quot;'SCHEDULED
gt;gt; INJ'!AL2:ALquot;amp;A1)=D3),--(INDIRECT(quot;'SCHEDULED
gt;gt; INJ'!H2:Hquot;amp;A1)=quot;30000001PCquot;),INDIRECT(quot;'SCHEDULED INJ'!AM2:AMquot;amp;A1))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;SDquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi there,
gt;gt; gt;
gt;gt; gt; I have a forumula
gt;gt; gt;
gt;gt; gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt;gt; gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt;gt; gt;
gt;gt; gt; My problem is: because it is an array the row number 55 needs be the
gt;gt; gt; last
gt;gt; gt; line (ie there cant be any blanks after the last bit of data). This
gt;gt; gt; formula
gt;gt; gt; references a query so the number of rows changes daily, one day it
gt;gt; gt; might
gt;gt; gt; be
gt;gt; gt; 70 next 55. How can I get this to reference the exact number of rows
gt;gt; gt; in
gt;gt; gt; quot;scheduled inj quot;worksheets.
gt;gt; gt;
gt;gt; gt; I have a row count set up but need this formula to reference it.
gt;gt; gt;
gt;gt; gt; cheers
gt;gt; gt;
gt;gt; gt; SD
gt;gt;
gt;gt;
gt;gt;
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.

quot;JMBquot; wrote:

gt; you could use INDIRECT
gt;
gt; =INDIRECT(quot;Aquot;amp;C1)
gt;
gt; where C1 contains the row number.
gt;
gt; you could also look at dynamic named ranges
gt;
gt; www.cpearson.com/excel/excelF.htm#DynamicRanges
gt;
gt;
gt; quot;SDquot; wrote:
gt;
gt; gt; Hi there,
gt; gt;
gt; gt; I have a forumula
gt; gt;
gt; gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt; gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt; gt;
gt; gt; My problem is: because it is an array the row number 55 needs be the last
gt; gt; line (ie there cant be any blanks after the last bit of data). This formula
gt; gt; references a query so the number of rows changes daily, one day it might be
gt; gt; 70 next 55. How can I get this to reference the exact number of rows in
gt; gt; quot;scheduled inj quot;worksheets.
gt; gt;
gt; gt; I have a row count set up but need this formula to reference it.
gt; gt;
gt; gt; cheers
gt; gt;
gt; gt; SD

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

www.contextures.on.ca/xlDataVal13.html

Download the sample file for a working example.Gord Dibben MS Excel MVP

On Mon, 7 Aug 2006 17:42:02 -0700, andy62 gt;
wrote:

gt;I checked Chip's website and have used his formula for creating a dynamic
gt;named range. But I can't find any way to do this: when I used a dynamic
gt;named range in a data validation, I would like the items to appear in
gt;alphabetical order (even though the items in the list are not). Is there any
gt;version of the dynamic named range formula that would alphabetize the list?
gt;Thanks in advance.
gt;
gt;quot;JMBquot; wrote:
gt;
gt;gt; you could use INDIRECT
gt;gt;
gt;gt; =INDIRECT(quot;Aquot;amp;C1)
gt;gt;
gt;gt; where C1 contains the row number.
gt;gt;
gt;gt; you could also look at dynamic named ranges
gt;gt;
gt;gt; www.cpearson.com/excel/excelF.htm#DynamicRanges
gt;gt;
gt;gt;
gt;gt; quot;SDquot; wrote:
gt;gt;
gt;gt; gt; Hi there,
gt;gt; gt;
gt;gt; gt; I have a forumula
gt;gt; gt;
gt;gt; gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt;gt; gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt;gt; gt;
gt;gt; gt; My problem is: because it is an array the row number 55 needs be the last
gt;gt; gt; line (ie there cant be any blanks after the last bit of data). This formula
gt;gt; gt; references a query so the number of rows changes daily, one day it might be
gt;gt; gt; 70 next 55. How can I get this to reference the exact number of rows in
gt;gt; gt; quot;scheduled inj quot;worksheets.
gt;gt; gt;
gt;gt; gt; I have a row count set up but need this formula to reference it.
gt;gt; gt;
gt;gt; gt; cheers
gt;gt; gt;
gt;gt; gt; SDSorry 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!

quot;Gord Dibbenquot; wrote:

gt; Debra Dalgleish has instructions for autosorting a drop-down list.
gt;
gt; www.contextures.on.ca/xlDataVal13.html
gt;
gt; Download the sample file for a working example.
gt;
gt;
gt; Gord Dibben MS Excel MVP
gt;
gt; On Mon, 7 Aug 2006 17:42:02 -0700, andy62 gt;
gt; wrote:
gt;
gt; gt;I checked Chip's website and have used his formula for creating a dynamic
gt; gt;named range. But I can't find any way to do this: when I used a dynamic
gt; gt;named range in a data validation, I would like the items to appear in
gt; gt;alphabetical order (even though the items in the list are not). Is there any
gt; gt;version of the dynamic named range formula that would alphabetize the list?
gt; gt;Thanks in advance.
gt; gt;
gt; gt;quot;JMBquot; wrote:
gt; gt;
gt; gt;gt; you could use INDIRECT
gt; gt;gt;
gt; gt;gt; =INDIRECT(quot;Aquot;amp;C1)
gt; gt;gt;
gt; gt;gt; where C1 contains the row number.
gt; gt;gt;
gt; gt;gt; you could also look at dynamic named ranges
gt; gt;gt;
gt; gt;gt; www.cpearson.com/excel/excelF.htm#DynamicRanges
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;SDquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; Hi there,
gt; gt;gt; gt;
gt; gt;gt; gt; I have a forumula
gt; gt;gt; gt;
gt; gt;gt; gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt; gt;gt; gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt; gt;gt; gt;
gt; gt;gt; gt; My problem is: because it is an array the row number 55 needs be the last
gt; gt;gt; gt; line (ie there cant be any blanks after the last bit of data). This formula
gt; gt;gt; gt; references a query so the number of rows changes daily, one day it might be
gt; gt;gt; gt; 70 next 55. How can I get this to reference the exact number of rows in
gt; gt;gt; gt; quot;scheduled inj quot;worksheets.
gt; gt;gt; gt;
gt; gt;gt; gt; I have a row count set up but need this formula to reference it.
gt; gt;gt; gt;
gt; gt;gt; gt; cheers
gt; gt;gt; gt;
gt; gt;gt; gt; SD
gt;
gt;

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

quot;andy62quot; gt; wrote in message
...
gt; Sorry to appear helpless, but I don't see anything on her site or in the
gt; zipped file that addresses alphabeticizing the items in the drop-down.
gt; But
gt; I'm encouraged to learn it might be possible!
gt;
gt; quot;Gord Dibbenquot; wrote:
gt;
gt;gt; Debra Dalgleish has instructions for autosorting a drop-down list.
gt;gt;
gt;gt; www.contextures.on.ca/xlDataVal13.html
gt;gt;
gt;gt; Download the sample file for a working example.
gt;gt;
gt;gt;
gt;gt; Gord Dibben MS Excel MVP
gt;gt;
gt;gt; On Mon, 7 Aug 2006 17:42:02 -0700, andy62
gt;gt; gt;
gt;gt; wrote:
gt;gt;
gt;gt; gt;I checked Chip's website and have used his formula for creating a
gt;gt; gt;dynamic
gt;gt; gt;named range. But I can't find any way to do this: when I used a dynamic
gt;gt; gt;named range in a data validation, I would like the items to appear in
gt;gt; gt;alphabetical order (even though the items in the list are not). Is
gt;gt; gt;there any
gt;gt; gt;version of the dynamic named range formula that would alphabetize the
gt;gt; gt;list?
gt;gt; gt;Thanks in advance.
gt;gt; gt;
gt;gt; gt;quot;JMBquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; you could use INDIRECT
gt;gt; gt;gt;
gt;gt; gt;gt; =INDIRECT(quot;Aquot;amp;C1)
gt;gt; gt;gt;
gt;gt; gt;gt; where C1 contains the row number.
gt;gt; gt;gt;
gt;gt; gt;gt; you could also look at dynamic named ranges
gt;gt; gt;gt;
gt;gt; gt;gt; www.cpearson.com/excel/excelF.htm#DynamicRanges
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;SDquot; wrote:
gt;gt; gt;gt;
gt;gt; gt;gt; gt; Hi there,
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I have a forumula
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt;gt; gt;gt; gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; My problem is: because it is an array the row number 55 needs be the
gt;gt; gt;gt; gt; last
gt;gt; gt;gt; gt; line (ie there cant be any blanks after the last bit of data). This
gt;gt; gt;gt; gt; formula
gt;gt; gt;gt; gt; references a query so the number of rows changes daily, one day it
gt;gt; gt;gt; gt; might be
gt;gt; gt;gt; gt; 70 next 55. How can I get this to reference the exact number of
gt;gt; gt;gt; gt; rows in
gt;gt; gt;gt; gt; quot;scheduled inj quot;worksheets.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I have a row count set up but need this formula to reference it.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; cheers
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; SD
gt;gt;
gt;gt;


quot;SDquot; wrote:

gt; Hi there,
gt;
gt; I have a forumula
gt;
gt; =SUM(IF('SCHEDULED INJ'!$AL$2:$AL$55=D3,1,0)*IF('SCHEDULED
gt; INJ'!$H$2:$H$55=quot;30000001PCquot;,1,0)*('SCHEDULED INJ'!$AM$2:$AM$55))
gt;
gt; My problem is: because it is an array the row number 55 needs be the last
gt; line (ie there cant be any blanks after the last bit of data). This formula
gt; references a query so the number of rows changes daily, one day it might be
gt; 70 next 55. How can I get this to reference the exact number of rows in
gt; quot;scheduled inj quot;worksheets.
gt;
gt; I have a row count set up but need this formula to reference it.
gt;
gt; cheers
gt;
gt; SD