|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 01:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com