From: Ayo on
What doesn't the 1E100 do in the formula?

"T. Valko" wrote:

> >T5 contains a date
>
> Ok, but that didn't answer my questions.
>
> So, try this...
>
> Create these named ranges
>
> Insert>Name>Define
> Name: Dates
> Refers to:
>
> =Current_MarketList!$I$5:INDEX(Current_MarketList!$I$5:$I$2000,MATCH(1E100,Current_MarketList!$I$5:$I$2000))
>
> Adjust for a reasonable end of range $I$2000
>
> Name: Status
> Refers to:
>
> =Current_MarketList!$K$5:INDEX(Current_MarketList!$K$5:$K$2000,MATCH(1E100,Current_MarketList!$I$5:$I$2000))
>
> Adjust for a reasonable end of ranges $K$2000 and $I$2000
>
> OK out
>
> Then:
>
> =SUMPRODUCT(--(Dates<$T$5),--(Status="PAST DUE"))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
> news:8A2370D2-0BE4-4632-9AB7-D09966EF37B4(a)microsoft.com...
> > T5 contains a date
> >
> > "T. Valko" wrote:
> >
> >> You can use a dynamic range.
> >>
> >> Are there any empty cells *within* the range I5:I2000? It looks like that
> >> range contains numbers, are there any text entries in that range?
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
> >> news:E9C12940-4BCE-4BA6-9208-69EB4E1C727A(a)microsoft.com...
> >> > Is there a function in excel that would allow me to replace the $2000
> >> > in
> >> > the
> >> > formular below with the last row in the column contain values.
> >> > Somthing like this, in VBA:
> >> > errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
> >> >
> >> > =SUMPRODUCT((Current_MarketList!$I$5:$I$2000<$T$5)*(Current_MarketList!$K$5:$K$2000="PAST
> >> > DUE"))
> >>
> >>
> >> .
> >>
>
>
> .
>
From: Ashish Mathur on
Hi,

Select I4:K2000 (or the entire range including more columns to the
left/right) and convert it to a List/Table (Ctrl+L) - this feature is
available Excel 2003 onwards. I have assumed row 4 has headers.

When you convert a range to a List, it becomes auto expanding. The caveat
here is that data should be entered in consecutive rows I.e. no row should
be left blank

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
news:E9C12940-4BCE-4BA6-9208-69EB4E1C727A(a)microsoft.com...
> Is there a function in excel that would allow me to replace the $2000 in
> the
> formular below with the last row in the column contain values.
> Somthing like this, in VBA:
> errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
>
> =SUMPRODUCT((Current_MarketList!$I$5:$I$2000<$T$5)*(Current_MarketList!$K$5:$K$2000="PAST
> DUE"))

From: T. Valko on
1E100 is scientific notation for a very large number. It's used to find the
last numeric value in the range.

--
Biff
Microsoft Excel MVP


"Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
news:351215B2-B1EB-495D-A9FA-0DBF6AD49A50(a)microsoft.com...
> What doesn't the 1E100 do in the formula?
>
> "T. Valko" wrote:
>
>> >T5 contains a date
>>
>> Ok, but that didn't answer my questions.
>>
>> So, try this...
>>
>> Create these named ranges
>>
>> Insert>Name>Define
>> Name: Dates
>> Refers to:
>>
>> =Current_MarketList!$I$5:INDEX(Current_MarketList!$I$5:$I$2000,MATCH(1E100,Current_MarketList!$I$5:$I$2000))
>>
>> Adjust for a reasonable end of range $I$2000
>>
>> Name: Status
>> Refers to:
>>
>> =Current_MarketList!$K$5:INDEX(Current_MarketList!$K$5:$K$2000,MATCH(1E100,Current_MarketList!$I$5:$I$2000))
>>
>> Adjust for a reasonable end of ranges $K$2000 and $I$2000
>>
>> OK out
>>
>> Then:
>>
>> =SUMPRODUCT(--(Dates<$T$5),--(Status="PAST DUE"))
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
>> news:8A2370D2-0BE4-4632-9AB7-D09966EF37B4(a)microsoft.com...
>> > T5 contains a date
>> >
>> > "T. Valko" wrote:
>> >
>> >> You can use a dynamic range.
>> >>
>> >> Are there any empty cells *within* the range I5:I2000? It looks like
>> >> that
>> >> range contains numbers, are there any text entries in that range?
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
>> >> news:E9C12940-4BCE-4BA6-9208-69EB4E1C727A(a)microsoft.com...
>> >> > Is there a function in excel that would allow me to replace the
>> >> > $2000
>> >> > in
>> >> > the
>> >> > formular below with the last row in the column contain values.
>> >> > Somthing like this, in VBA:
>> >> > errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
>> >> >
>> >> > =SUMPRODUCT((Current_MarketList!$I$5:$I$2000<$T$5)*(Current_MarketList!$K$5:$K$2000="PAST
>> >> > DUE"))
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>