|
row number question in an array
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 |
row number question in an array
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 "SD" wrote: 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 |
row number question in an array
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 "SD" wrote in message ... 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 |
row number question in an array
Thanks very much...can i ask what the -- means next to indirect???
"Biff" wrote: 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 "SD" wrote in message ... 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 |
row number question in an array
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 "SD" wrote in message ... Thanks very much...can i ask what the -- means next to indirect??? "Biff" wrote: 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 "SD" wrote in message ... 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 |
row number question in an array
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. "JMB" wrote: 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 "SD" wrote: 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 |
row number question in an array
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 On Mon, 7 Aug 2006 17:42:02 -0700, andy62 wrote: 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. "JMB" wrote: 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 "SD" wrote: 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 |
row number question in an array
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! "Gord Dibben" wrote: 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 On Mon, 7 Aug 2006 17:42:02 -0700, andy62 wrote: 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. "JMB" wrote: 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 "SD" wrote: 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 |
row number question in an array
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 "andy62" wrote in message ... 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! "Gord Dibben" wrote: 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 On Mon, 7 Aug 2006 17:42:02 -0700, andy62 wrote: 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. "JMB" wrote: 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 "SD" wrote: 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 |
row number question in an array
"SD" wrote: 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 |
| All times are GMT +1. The time now is 09:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com