Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Barb R.
 
Posts: n/a
Default Variable Sheet Name in Formula

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
  #2   Report Post  
KL
 
Posts: n/a
Default

Hi Barb,

Try this:

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

Regards,
KL


"Barb R." wrote in message
...
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



  #3   Report Post  
KL
 
Posts: n/a
Default

sorry, change B1 to A1:

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

KL


"KL" wrote in message
...
Hi Barb,

Try this:

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

Regards,
KL


"Barb R." wrote in message
...
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





  #4   Report Post  
Ajay
 
Posts: n/a
Default

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" wrote:

sorry, change B1 to A1:

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

KL


"KL" wrote in message
...
Hi Barb,

Try this:

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

Regards,
KL


"Barb R." wrote in message
...
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






  #5   Report Post  
KL
 
Posts: n/a
Default

Hi Ajay,

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

Regards,
KL


"Ajay" wrote in message
...
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" wrote:

sorry, change B1 to A1:

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

KL


"KL" wrote in message
...
Hi Barb,

Try this:

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

Regards,
KL


"Barb R." wrote in message
...
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









  #6   Report Post  
Ajay
 
Posts: n/a
Default

D'oh!!!!
Thanks KL
Ajay

"KL" wrote:

Hi Ajay,

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

Regards,
KL


"Ajay" wrote in message
...
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" wrote:

sorry, change B1 to A1:

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

KL


"KL" wrote in message
...
Hi Barb,

Try this:

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

Regards,
KL


"Barb R." wrote in message
...
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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Lookup cell contents in on sheet based on a formula in second sheet Michael Wright via OfficeKB.com Excel Worksheet Functions 1 April 30th 05 04:11 PM
How do I drag a formula so 1 variable changes and 1 stays on a cel keithpt Excel Discussion (Misc queries) 2 March 17th 05 04:47 AM
Can I use a variable to call up another sheet in Excel? GD Cooley Excel Worksheet Functions 1 February 13th 05 06:40 AM
Can I use a variable to call up another sheet in Excel? GD Cooley Excel Worksheet Functions 0 February 11th 05 03:47 PM


All times are GMT +1. The time now is 09:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"