Indirect & small

[LeeBillington]

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?

 =IF(ISERROR(SMALL(Pickrates!$M$6:$M$326,B6))," ",IF(SMALL(Pickrates!$M$6:$M$326,B6)<=249,SMALL(Pickrates!$M$6:$M$327,B6),""))

The original ^

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

My try ^

thanks.

[JohnTopley]

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

[LeeBillington]

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

[JohnTopley]

Which cell?

If G2 then do

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

if B6 then same as above

[LeeBillington]

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

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

[José Augusto]

Hi

I suppose you can use this formula

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

to correct your try.

[JohnTopley]

Using Jose's formula:

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

[LeeBillington]

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

[JohnTopley]

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

[LeeBillington]

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?

[José Augusto]

Hi
Try this

[JeteMc]

Hello Lee,

This has been sitting unanswered for a couple of days and it appears that it is hard to understand what is going on. Suggest that you upload a sample that demonstrates the issue you are having with one cell displaying a #VALUE error when another cell is blank.
To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.