SMALL across multiple sheets

[Benjaminmin]

Hi,

I am doing a workbook, with several sheets with various share prices.

I am now trying to find the third worst performing share across a range of sheets, my formula is:

SMALL((AstraZeneca!AB4,'BG Group'!AB4,BHP!AB4,BP!AB4,BAT!AB4,Glaxo!AB4,HSBC!AB4,'Rio T'!AB4,'Shell A'!AB4,Vodafone!AB4),3)

But that returns a #value!.

Can anyone see something wrong, or can you simply not use SMALL on something thats not an array?

If thats the case, does anyone have an alternate solution to finding the third smallest value please?

Thanks in advance

 

[Benjaminmin]

Just to clarify, the formula works fine if the numbers are all in the same sheet (even if theyre not next to each other), it is when they are on different sheets that they return an error.

Any help is GREATLY appreciated.

Thanks

 

[pgc01]

Hi

A multi-area range must have all the cells in the same worksheet.

A simple solution is to have all the worksheets contiguous. If the 10 worksheets are contiguous, with AstraZeneca the first and Vodafone the last, you can use a 3D formula:

=SMALL('AstraZeneca:Vodafone'!AB4,3)

 

[Benjaminmin]

Hi

A multi-area range must have all the cells in the same worksheet.

A simple solution is to have all the worksheets contiguous. If the 10 worksheets are contiguous, with AstraZeneca the first and Vodafone the last, you can use a 3D formula:

=SMALL('AstraZeneca:Vodafone'!AB4,3)

Works like a charm! Thanks a million mate!

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SMALL(CHOOSE(ROW(INDIRECT("1:10")),AstraZeneca!AB4,'BG Group'!AB4,BHP!AB4,BP!AB4,BAT!AB4,Glaxo!AB4,HSBC!AB4,'Rio T'!AB4,'Shell A'!AB4,Vodafone!AB4),3)

The ROW bit can be replaced with:

{1,2,3,4,5,6,7,8,9,10}

if so desired.

Or, just enter:

=SMALL(First:Last!AB4,3)

where First and Last are two additional sheets envelopping the relevant sheets you enumerated.

 

[T. Valko]

Just to clarify, the formula works fine if the numbers are all in the same sheet (even if theyre not next to each other), it is when they are on different sheets that they return an error.

Any help is GREATLY appreciated.

Thanks

Yeah, that's how it works. If there are multiple area references they have to be on the same sheet.

Maybe pull all the values to a common sheet then do:

=SMALL(A1:A10,3)