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

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

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

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))

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))

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?

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))

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.

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.

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.

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.

"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.

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?

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

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?

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?

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?

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! :)