Indirect & small

Hi guys, so i am basically trying to incorporate indirect into the formula below.. but i am not doing very well,
someone mind fixing my error so i can learn from it?

Please Login or Register  to view this content.

The original ^

Please Login or Register  to view this content.

My try ^

thanks.

Try

=IF(IFERROR(SMALL(INDIRECT("'" &G2 &"'!$M$6:$M$326"),B6)," "),IF(SMALL(INDIRECT("'" &G2 &"'!$M$6:$M$326"),B6)<=249,SMALL(INDIRECT("'" &G2 &"'!$M$6:$M$326"),B6),""))

it works thanks, but also returns #VALUE! if the cell is empty.. way to get rid of that?

Which cell?

If G2 then do

=IF(G2="","",formula)

if B6 then same as above

Hi

I suppose you can use this formula

Formula:

Please Login or Register  to view this content.

to correct your try.

But if the formula already has iferror how can i add =IF(B6="","",formula)

Please Login or Register  to view this content.

Using Jose's formula:

=IF(B6="","",IFERROR(SMALL(INDIRECT("'" & G2 &"'!$M$6:$M$13"),B6)," "))

He's doesn't have the <=249, tho?

then ...



=IF(B6="","",IF(IFERROR(SMALL(INDIRECT("'" &G2 &"'!$M$6:$M$326"),B6)," "),IF(SMALL(INDIRECT("'" &G2 &"'!$M$6:$M$326"),B6)<=249,SMALL(INDIRECT("'" &G2 &"'!$M$6:$M$326"),B6),"")))

Hi john, sorry to bother you again.. but i been away from this post and only just looked back at this workbook, that formula still returns #VALUE! if there is no data?