To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

Can this formula be modify below to work with number & Text values?
Because in column A has mix numbers and Texts and I like it to return
either one.

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

I'm using this formula to return the results.

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

I got this solution from the link below.


http://groups.google.com/group/micro...6dd080333efba5

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 9, 6:41 am, "Fin Fang Foom" wrote:
Can this formula be modify below to work with number & Text values?
Because in column A has mix numbers and Texts and I like it to return
either one.

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

I'm using this formula to return the results.

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

I got this solution from the link below.

http://groups.google.com/group/micro...orksheet.funct...



bump

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

Maybe...

1) Define (Insert Name Define) Array as follows...

=INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N)))

2) Change the reference for the defined name Col_A, as follows...

=IF(COUNTIF(Array,"=-9.99999999999999E+307"),N(Array),T(Array))

Hope this helps!

In article .com,
"Fin Fang Foom" wrote:

Can this formula be modify below to work with number & Text values?
Because in column A has mix numbers and Texts and I like it to return
either one.

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

I'm using this formula to return the results.

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

I got this solution from the link below.


http://groups.google.com/group/micro...functions/brow
se_thread/thread/b1acf05749ff2781/256dd080333efba5?lnk=gst&q=Small(IF(+Across+
worksheets++formula+excel&rnum=1&hl=en#256dd080333 efba5

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

Domenic wrote...
....
=IF(COUNTIF(Array,"=-9.99999999999999E+307"),N(Array),T(Array))
....

It's a pain that COUNTIF and SUMIF are the only functions that can
work directly with arrays of range references, but it's easier to test
that something's not text. Try

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

Thanks Harlan! My apologies. I forgot to test it. It looks like INDEX
will not accept the array, in either case. Any ideas?

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
...
=IF(COUNTIF(Array,"=-9.99999999999999E+307"),N(Array),T(Array))
...

It's a pain that COUNTIF and SUMIF are the only functions that can
work directly with arrays of range references, but it's easier to test
that something's not text. Try

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 9, 1:53 pm, Domenic wrote:
Thanks Harlan! My apologies. I forgot to test it. It looks like INDEX
will not accept the array, in either case. Any ideas?

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
...
=IF(COUNTIF(Array,"=-9.99999999999999E+307"),N(Array),T(Array))
...

It's a pain that COUNTIF and SUMIF are the only functions that can
work directly with arrays of range references, but it's easier to test
that something's not text. Try

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))



Thank You for repyling. I tried both suggestions but no avail. Do you
think we could get this to work?

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

I tried using LOOKUP instead of INDEX but unfortunately it too doesn't
like the array defined by Col_A. It returns #N/A. Here's the formula I
tried...

=LOOKUP(SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2)),S ,Col_A)

....confirmed with CONTROL+SHIFT+ENTER.

In article ,
Domenic wrote:

Thanks Harlan! My apologies. I forgot to test it. It looks like INDEX
will not accept the array, in either case. Any ideas?

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
...
=IF(COUNTIF(Array,"=-9.99999999999999E+307"),N(Array),T(Array))
...

It's a pain that COUNTIF and SUMIF are the only functions that can
work directly with arrays of range references, but it's easier to test
that something's not text. Try

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 9, 6:04 pm, Domenic wrote:
I tried using LOOKUP instead of INDEX but unfortunately it too doesn't
like the array defined by Col_A. It returns #N/A. Here's the formula I
tried...

=LOOKUP(SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2)),S ,Col_A)

...confirmed with CONTROL+SHIFT+ENTER.

In article ,

Domenic wrote:
Thanks Harlan! My apologies. I forgot to test it. It looks like INDEX
will not accept the array, in either case. Any ideas?

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
...
=IF(COUNTIF(Array,"=-9.99999999999999E+307"),N(Array),T(Array))
...

It's a pain that COUNTIF and SUMIF are the only functions that can
work directly with arrays of range references, but it's easier to test
that something's not text. Try

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))


Thank you Domenic. I'll try it right now and post back.

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 9, 6:11 pm, "Fin Fang Foom" wrote:
On Feb 9, 6:04 pm, Domenic wrote:



I tried using LOOKUP instead of INDEX but unfortunately it too doesn't
like the array defined by Col_A. It returns #N/A. Here's the formula I
tried...

=LOOKUP(SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2)),S ,Col_A)

...confirmed with CONTROL+SHIFT+ENTER.

In article ,

Domenic wrote:
Thanks Harlan! My apologies. I forgot to test it. It looks like INDEX
will not accept the array, in either case. Any ideas?

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
...
=IF(COUNTIF(Array,"=-9.99999999999999E+307"),N(Array),T(Array))
...

It's a pain that COUNTIF and SUMIF are the only functions that can
work directly with arrays of range references, but it's easier to test
that something's not text. Try

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))

Thank you Domenic. I'll try it right now and post back.


Domenic,

Did the formula you provied work for you? I could not get it to work.

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

In article .com,
"Fin Fang Foom" wrote:

Domenic,

Did the formula you provied work for you? I could not get it to work.

No, it doesn't work. I merely mentioned that I tried the LOOKUP formula
and that it too didn't work. It looks like both LOOKUP and INDEX don't
want to accept the new array defined as Col_A.

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 9, 7:10 pm, Domenic wrote:
In article .com,
"Fin Fang Foom" wrote:

Domenic,

Did the formula you provied work for you? I could not get it to work.

No, it doesn't work. I merely mentioned that I tried the LOOKUP formula
and that it too didn't work. It looks like both LOOKUP and INDEX don't
want to accept the new array defined as Col_A.


Ok sorry about that I misunderstood. Thank For replying.
Hopefuly Harlan Grove would come up with something.

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

"Fin Fang Foom" wrote...
....
Thank You for repyling. I tried both suggestions but no avail. Do you
think we could get this to work?

With Array defined as

=INDIRECT("'"&WSLST&"'!A"&(2+MOD(S,N)))

with S and N single numeric values (scalars) the array formula

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))

returns an array of each A# cell in each of the worksheets named in
WSLST, where # is 2+MOD(S,N).

Where does your other formula,

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

come into play? And what are S and N? And what are Col_A and Col_B?
Looks like S is an array.

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 10, 12:26 am, "Harlan Grove" wrote:
"Fin Fang Foom" wrote...
...

Thank You for repyling. I tried both suggestions but no avail. Do you
think we could get this to work?

With Array defined as

=INDIRECT("'"&WSLST&"'!A"&(2+MOD(S,N)))

with S and N single numeric values (scalars) the array formula

=IF(COUNTIF(Array,"<*"),N(Array),T(Array))

returns an array of each A# cell in each of the worksheets named in
WSLST, where # is 2+MOD(S,N).

Where does your other formula,

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

come into play? And what are S and N? And what are Col_A and Col_B?
Looks like S is an array.

You have helped me before with these formulas.

Define Names...

Col_A =T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))
Col_B =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N))))
N =8
S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
WSLST =Sheet1!$A$2:$A$3
XWSLST =T(OFFSET(WSLST,INT(S/N),0,1,1))

and using this formula

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

Check the link below.

http://groups.google.com/group/micro...6dd080333efba5

The only different now is column A contains texts and numbers.

Do you think the INDEX Function is the problem?

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

This seems to work...

=INDEX(IF(COUNTIF(INDIRECT("'"&XWSLST&"'!A"&(2+MOD (S,N))),"<*"),N(INDIRE
CT("'"&XWSLST&"'!A"&(2+MOD(S,N)))),T(INDIRECT("'"& XWSLST&"'!A"&(2+MOD(S,N
))))),SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1)

....confirmed with CONTROL+SHIFT+ENTER.

In article .com,
"Fin Fang Foom" wrote:

Can this formula be modify below to work with number & Text values?
Because in column A has mix numbers and Texts and I like it to return
either one.

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

I'm using this formula to return the results.

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

I got this solution from the link below.


http://groups.google.com/group/micro...functions/brow
se_thread/thread/b1acf05749ff2781/256dd080333efba5?lnk=gst&q=Small(IF(+Across+
worksheets++formula+excel&rnum=1&hl=en#256dd080333 efba5

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 10, 11:33 am, Domenic wrote:
This seems to work...

=INDEX(IF(COUNTIF(INDIRECT("'"&XWSLST&"'!A"&(2+MOD (S,N))),"<*"),N(INDIRE
CT("'"&XWSLST&"'!A"&(2+MOD(S,N)))),T(INDIRECT("'"& XWSLST&"'!A"&(2+MOD(S,N
))))),SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1)

...confirmed with CONTROL+SHIFT+ENTER.

In article .com,
"Fin Fang Foom" wrote:

Can this formula be modify below to work with number & Text values?
Because in column A has mix numbers and Texts and I like it to return
either one.

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

I'm using this formula to return the results.

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

I got this solution from the link below.

http://groups.google.com/group/micro...orksheet.funct...
se_thread/thread/b1acf05749ff2781/256dd080333efba5?lnk=gst&q=Small(IF(+Across+
worksheets++formula+excel&rnum=1&hl=en#256dd080333 efba5

Hi Domenic thank you so much.

I test it and it seems to work great. I notice the calculation seems
slower probably all these functions.
I was thinking can we great a Dynamic row reference. I know this part
of the array

N =575 is the number of rows.

But I have 7 worksheets in the workbook right now and each of those
worksheets the data fluctuates. Some are at row 280 and other might be
at row 563.

Is there a way to great a dynamic name range to find the max row in
one of those worksheets and use that as the final reference?

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 10, 11:33 am, Domenic wrote:
This seems to work...

=INDEX(IF(COUNTIF(INDIRECT("'"&XWSLST&"'!A"&(2+MOD (S,N))),"<*"),N(INDIRE
CT("'"&XWSLST&"'!A"&(2+MOD(S,N)))),T(INDIRECT("'"& XWSLST&"'!A"&(2+MOD(S,N
))))),SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1)

...confirmed with CONTROL+SHIFT+ENTER.

In article .com,
"Fin Fang Foom" wrote:

Can this formula be modify below to work with number & Text values?
Because in column A has mix numbers and Texts and I like it to return
either one.

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

I'm using this formula to return the results.

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)

I got this solution from the link below.

http://groups.google.com/group/micro...orksheet.funct...
se_thread/thread/b1acf05749ff2781/256dd080333efba5?lnk=gst&q=Small(IF(+Across+
worksheets++formula+excel&rnum=1&hl=en#256dd080333 efba5

Hi Domenic thank you so much.

I test it and it seems to work great. I notice the calculation seems
slower probably of all these functions we're using.
I was thinking can we great a Dynamic row reference.
I know in this part of the array

N =575

is the number of rows.

But I have 7 worksheets in the workbook right now and each of those
worksheets the data fluctuates. Some are at row 280 and other might be
at row 563.

Is there a way to create a dynamic name range to find the max row in
one of those worksheets and use that as the final row reference?

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

Assuming that data will never be present beyond Row 1000, try...

N:

=MAX(IF(COUNTIF(OFFSET(INDIRECT("'"&TRANSPOSE(WSLS T)&"'!A2:A1000"),ROW(IN
DIRECT("2:1000"))-2,,1),"<"),ROW(INDIRECT("2:1000"))-2,""))+1

Adjust the range accordingly. Note that the maximum number of rows is
based on Column A.

Hope this helps!

In article .com,
"Fin Fang Foom" wrote:

Hi Domenic thank you so much.

I test it and it seems to work great. I notice the calculation seems
slower probably of all these functions we're using.
I was thinking can we great a Dynamic row reference.
I know in this part of the array

N =575

is the number of rows.

But I have 7 worksheets in the workbook right now and each of those
worksheets the data fluctuates. Some are at row 280 and other might be
at row 563.

Is there a way to create a dynamic name range to find the max row in
one of those worksheets and use that as the final row reference?

To have this formula work with numbers & Text ='T(INDIRECT("'"&
 

On Feb 10, 5:44 pm, Domenic wrote:
Assuming that data will never be present beyond Row 1000, try...

N:

=MAX(IF(COUNTIF(OFFSET(INDIRECT("'"&TRANSPOSE(WSLS T)&"'!A2:A1000"),ROW(IN
DIRECT("2:1000"))-2,,1),"<"),ROW(INDIRECT("2:1000"))-2,""))+1

Adjust the range accordingly. Note that the maximum number of rows is
based on Column A.

Hope this helps!

In article .com,
"Fin Fang Foom" wrote:



Hi Domenic thank you so much.

I test it and it seems to work great. I notice the calculation seems
slower probably of all these functions we're using.
I was thinking can we great a Dynamic row reference.
I know in this part of the array

N =575

is the number of rows.

But I have 7 worksheets in the workbook right now and each of those
worksheets the data fluctuates. Some are at row 280 and other might be
at row 563.

Is there a way to create a dynamic name range to find the max row in
one of those worksheets and use that as the final row reference?- Hide quoted text -

- Show quoted text -

Thanks Domenic it works great! :)