Variable Sheet Name in Formula

[Guest]

This is a continuation of a question from Ajay.

Let's say we have 20 sheets named sheet1-sheet20
We have a column of data with each of the sheet names.
A1=Sheet1
A2=Sheet2 and so on.

In B1, I want to calculate the MIN of the values in column B on SHeet 1
In B2, I want the MIN of values in column B on Sheet2
In B3, I want the MIN of values in column B on Sheet3

How would I write the MIN statement to use a variable SHEET name?

Thanks in advance,
Barb Reinhardt

 

[KL]

Hi Barb,

Try this:

=MIN(INDIRECT("'"&B1&"'!B:B"))

Regards,
KL

 

[KL]

sorry, change B1 to A1:

=MIN(INDIRECT("'"&A1&"'!B:B"))

KL

 

[Guest]

Hi KL
Ajay here trying to follow your suggestion and I think I am probably typing
it in wrong.
The first sheet is named 339463 this is first on my list
so should this look like:
=MIN(INDIRECT("'"&A1&"'!,B:B")) where A1 is my first sheet name (339463)
I have entered this and get #REF! Wot am I doing wrong
TIA
Ajay

 

[KL]

Hi Ajay,

Just remove the coma (,) between "!" and "B:B"

Regards,
KL

 

[Guest]

D'oh!!!!
Thanks KL
Ajay

Hi Ajay,

Just remove the coma (,) between "!" and "B:B"

Regards,
KL