SMALL function excluding specific cell

[plotting]

https://1drv.ms/x/s!ArHhL08110anmBjx0Xyj3khzBu0S

Here is a link to the data set, sheets1:5 have random numbers in A1:N10

On sheet6 it uses =SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)) to =SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:20)) to get the 20 smallest numbers in the range, wondering anyone has a formula, array perhaps, to ignore sheet5 cell F8 for example.

Thanks in advance for looking!

 

[oldbrewer]

add 10^50 to sheet5!F8 ? or make it text...

 

[plotting]

add 10^50 to sheet5!F8 ? or make it text...

The cell is used in a formula elsewhere so neither of those would work

 

[oldbrewer]

4	5	6
2	3	4
9	8	7
				45
	in this example range1 is all the cells except the 3 in B2
	the 45 is obtained by =sum(range1)

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[plotting]

4	5	6
2	3	4
9	8	7
				45
	in this example range1 is all the cells except the 3 in B2
	the 45 is obtained by =sum(range1)

<tbody>
</tbody>

How do you make a range in name manager without a specific cell in it short of doing ='Sheet1'$A$1!,'Sheet1'$A$2!, ect. I'm guessing that's what you're referring to

 

[plotting]

4	5	6
2	3	4
9	8	7
				45
	in this example range1 is all the cells except the 3 in B2
	the 45 is obtained by =sum(range1)

<tbody>
</tbody>

I think I tried named ranges and it doesn't like ='Sheet1:Sheet4'!$A$1:$N$10,'Sheet5'!$A$1:$E$10,'Sheet5'!$F$1:$F$7,'Sheet5'!$F$9:$F$10,'Sheet5'!$G$1:$N$10 as a formula

 

[kvsrinivasamurthy]

Use column A as helper column. Can be hidden.
In A2 then drag down up to 50 rows.

=IF(SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1))<>Sheet5!$C$8,SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)),"")

Then in B2 formula then drag down till required. Column B gives result.

=IFERROR(SMALL($A$2:$A$32,ROW(1:1)),"")

 

[plotting]

Use column A as helper column. Can be hidden.
In A2 then drag down up to 50 rows.

=IF(SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1))<>Sheet5!$C$8,SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)),"")

Then in B2 formula then drag down till required. Column B gives result.

=IFERROR(SMALL($A$2:$A$32,ROW(1:1)),"")

That works great, I will use that for now unless I can find a cleaner way without using a helper column but more than accomplishes the task, thanks!

 

[Peter_SSs]

Try (no need for helper column)
=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)+(Sheet5!$F$8<=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1))))

 

[kvsrinivasamurthy]

If all sheets have similar name this ARRAY formula works.
In A2 then drag down.

<sheet5!$f$8,0,sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!$f$8))))[ code]

=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)+IF(SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1))< Sheet5!$F$8,0,SUM(COUNTIF(INDIRECT("Sheet"&ROW($1:$5)&"!A1:N10"),Sheet5!$F$8))))

<sheet5!$f$8,0,rows($1:1)+sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!c8))))[ code]
How ARRAY formula is entered


Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.</sheet5!$f$8,0,rows($1:1)+sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!c8))))[></sheet5!$f$8,0,sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!$f$8))))[>