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

[Fin Fang Foom]

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,C2))+1)

I got this solution from the link below.


http://groups.google.com/group/micr...++formula+excel&rnum=1&hl=en#256dd080333efba5

 

[Fin Fang Foom]

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,C2))+1)

I got this solution from the link below.

http://groups.google.com/group/microsoft.public.excel.worksheet.funct...

bump

 

[Domenic]

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!

 

[Harlan Grove]

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

 

[Domenic]

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

 

[Fin Fang Foom]

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

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

 

[Domenic]

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.

 

[Fin Fang Foom]

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.

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

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

 

[Fin Fang Foom]

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.

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


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.

 

[Domenic]

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.

 

[Fin Fang Foom]

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.

 

[Harlan Grove]

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,C2))+1)

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

 

[Fin Fang Foom]

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,C2))+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,C2))+1)

Check the link below.

http://groups.google.com/group/micr...ross+worksheets+excel&rnum=1#256dd080333efba5

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

Do you think the INDEX Function is the problem?

 

[Domenic]

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.

 

[Fin Fang Foom]

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.

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?

 

[Fin Fang Foom]

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.

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?

 

[Domenic]

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

N:

=MAX(IF(COUNTIF(OFFSET(INDIRECT("'"&TRANSPOSE(WSLST)&"'!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!

 

[Fin Fang Foom]

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

N:

=MAX(IF(COUNTIF(OFFSET(INDIRECT("'"&TRANSPOSE(WSLST)&"'!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!








- Show quoted text -

Thanks Domenic it works great!