Get values from worksheets

[DonLogan]

I have a Focus worksheet & worksheets E1 thru E9
E's have values in cells a1 thru f15
I need Focus a1 to contain the smallest value in any of the E sheets
and Focus b1 to contain what worksheet that value came from
and Focus a2 to contain the second lowest and Focus b2 contain the
worksheet it came from
and so on up to a10, b10

I've got to Focus a1 as
=MIN(E1!a1:f15,E2!a1:f15......

but what next
thanks

 

[T. Valko]

Try this:

Enter this formula in A1 and copy down to A10:

=SMALL('E1:E9'!A$1:F$15,ROWS(A$1:A1))

Enter this array formula** in B1 and copy down to B10:

=INDEX("E"&ROW(INDIRECT("1:9")),MATCH(TRUE,COUNTIF(INDIRECT("'"&"E"&ROW(INDIRECT("1:9"))&"'!A1:F15"),A1)>0,0))

Note: if a value appears on more than one sheet the formula will return the
*first* sheet name where the value is found.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

We can shorten that array formula a bit. No real need for the INDEX call:

="E"&MATCH(TRUE,COUNTIF(INDIRECT("'"&"E"&ROW(INDIRECT("1:9"))&"'!A1:F15"),A1)>0,0)

 

[DonLogan]

Worked just great
thank you very much

 

[T. Valko]

You're welcome. Thanks for the feedback!