Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Worksheets from Cell Values
Thanks in advance to anyone that can help me here.
I have a workbook that will have a varying amount of sheets with different names. The range in # of sheets will be 2-10. What I need to do is use the formula =MIN(Sheet1:Sheet2!A1) where A1 is the cell in each of the sheets prior to the one I am on, so that I can get the minimum from the that cell value for each of the sheets. The problem here is that I want the Sheet1:Sheet2 to not be static, but work based on values in one or two cells (however it needs to work). I have formulas that can give me the 1st sheet name and the next to last sheet name perfectly (the last sheet will be the one for which I will be running the Min formulas, etc). Can this be done? Even if I was only able to build a formula that could pull the information from the sheets preceding the one I was on (the active one), that would work too. Let me know if you need more information. Help is greatly appreciated. Conor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Worksheets from Cell Values
Function min_to_here() As Variant
Application.Volatile Dim s As String min_to_here = Sheets(1).Range("A1").Value s = Application.Caller.Parent.Name For i = 2 To Sheets.Count If Sheets(i).Name = s Then Exit Function End If If Sheets(i).Range("A1").Value < min_to_here Then min_to_here = Sheets(i).Range("A1").Value End If Next End Function We get the name of the sheet on which the function is located. We then examine the A1's on the sheets up to that sheet. -- Gary''s Student - gsnu2007b " wrote: Thanks in advance to anyone that can help me here. I have a workbook that will have a varying amount of sheets with different names. The range in # of sheets will be 2-10. What I need to do is use the formula =MIN(Sheet1:Sheet2!A1) where A1 is the cell in each of the sheets prior to the one I am on, so that I can get the minimum from the that cell value for each of the sheets. The problem here is that I want the Sheet1:Sheet2 to not be static, but work based on values in one or two cells (however it needs to work). I have formulas that can give me the 1st sheet name and the next to last sheet name perfectly (the last sheet will be the one for which I will be running the Min formulas, etc). Can this be done? Even if I was only able to build a formula that could pull the information from the sheets preceding the one I was on (the active one), that would work too. Let me know if you need more information. Help is greatly appreciated. Conor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Worksheets from Cell Values
No need to get too fancy here. The formula that you have will look at Sheet1
and Sheet2 and all sheets in between. So so long as any new sheets are added inbetween the sheets listed in the fromula you are good to go. To that end you want to add a couple of Dummy sheets to your workbook. Call them Start and End (or whatever) Place them ahead of sheet 1 and after sheet2. Change your formula to =MIN(Start:End!A1) You may need to spike the values in start and end so that they are not the minimums. Now hide the Start and End sheets. When new sheets are added they should land between start and end and since start and end are hidden you do not have to worry about users moving them (generateing wrong answers) or deleteing them (invalidating the formulas). -- HTH... Jim Thomlinson " wrote: Thanks in advance to anyone that can help me here. I have a workbook that will have a varying amount of sheets with different names. The range in # of sheets will be 2-10. What I need to do is use the formula =MIN(Sheet1:Sheet2!A1) where A1 is the cell in each of the sheets prior to the one I am on, so that I can get the minimum from the that cell value for each of the sheets. The problem here is that I want the Sheet1:Sheet2 to not be static, but work based on values in one or two cells (however it needs to work). I have formulas that can give me the 1st sheet name and the next to last sheet name perfectly (the last sheet will be the one for which I will be running the Min formulas, etc). Can this be done? Even if I was only able to build a formula that could pull the information from the sheets preceding the one I was on (the active one), that would work too. Let me know if you need more information. Help is greatly appreciated. Conor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Referencing between multiple worksheets | Excel Discussion (Misc queries) | |||
Cell referencing between differnt worksheets | Excel Discussion (Misc queries) | |||
Referencing Named Ranges using values in a cell | Excel Discussion (Misc queries) | |||
referencing same cell in several worksheets | Excel Discussion (Misc queries) | |||
referencing values in adjacent cells to selected cell | Excel Programming |