Home |
Search |
Today's Posts |
#1
|
|||
|
|||
indirect and offsetsheet
I want to write an Indirect formula to find a value in a cell (B54) across multiple worksheets. Previously I would write something like =Sum(indirect ($a1)&!B54) to find B54 across several sheets(and I'd copy the formula down to reference A2, A3, etc for each worksheet name. Then I'd put the sheet names in A1,A2,A3. When the sheet names are short & simple and you can use AutoFill it isn't a big deal. BUT I'd like to convert the formula to look for the offsetSheet, or IndexSheet so I could just look at B54 value for IndexSheets, 3,4,5... instead of typing the name of the worksheets in cells A1, A2, A3. I've tried several times to alter the formula, but I think I'm missing a ( or a comma. Thanks in advance -- JVLOLLAR ------------------------------------------------------------------------ JVLOLLAR's Profile: http://www.excelforum.com/member.php...o&userid=23721 View this thread: http://www.excelforum.com/showthread...hreadid=374087 |
#2
|
|||
|
|||
Try... =SUM('Sheet1:Sheet3'!B54) Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=374087 |
#3
|
|||
|
|||
I was given the following macro code (from an earlier posting) which has been
effective for me - I think it will help you. The code creates a sheet in the front of your document with a list of each tab name vertically. Then you can refer to these tab names instead of typing them each time. Sub GetMeAllSheetNames() Const cnsResultSheetName = "Sheet1" Const cnsStartColName = "b" Const cnsStartRow = "2" Dim bSheetWasHidden As Boolean Dim intRowNum As Integer intRowNum = cnsStartRow For Each oSheet In Application.Sheets 'If InStr(oSheet.Name, cnsResultSheetName) = 0 Then bSheetWasHidden = False If Sheets(oSheet.Name).Visible = False Then bSheetWasHidden = True Sheets(oSheet.Name).Visible = True End If Sheets(oSheet.Name).Select 'MsgBox oSheet.Name Sheets(cnsResultSheetName).Range(cnsStartColName & CStr(intRowNum)).Formula = CStr(oSheet.Name) intRowNum = intRowNum + 1 If bSheetWasHidden = True Then Sheets(oSheet.Name).Visible = False End If 'End If Next Sheets(cnsResultSheetName).Select Sheets(cnsResultSheetName).Range("A1").Select End Sub hth, Dave "JVLOLLAR" wrote: I want to write an Indirect formula to find a value in a cell (B54) across multiple worksheets. Previously I would write something like =Sum(indirect ($a1)&!B54) to find B54 across several sheets(and I'd copy the formula down to reference A2, A3, etc for each worksheet name. Then I'd put the sheet names in A1,A2,A3. When the sheet names are short & simple and you can use AutoFill it isn't a big deal. BUT I'd like to convert the formula to look for the offsetSheet, or IndexSheet so I could just look at B54 value for IndexSheets, 3,4,5... instead of typing the name of the worksheets in cells A1, A2, A3. I've tried several times to alter the formula, but I think I'm missing a ( or a comma. Thanks in advance -- JVLOLLAR ------------------------------------------------------------------------ JVLOLLAR's Profile: http://www.excelforum.com/member.php...o&userid=23721 View this thread: http://www.excelforum.com/showthread...hreadid=374087 |
#4
|
|||
|
|||
The formula suggested produced a #Ref error. The formula(s) I am trying to fix is/are =(INDIRECT(A4&"!D54")) =(INDIRECT(A5&"!D54")) =(INDIRECT(A6&"!D54")) this continues for about 50 sheets worth of rows Instead of typing in the specific Tab Name in A4,(and each subsequent cell below for each following tab name) I want to type in the Worksheet Position reference, if it is possible. So instead of having each tab name listed in the cells as: A4 Term1 A5 Hardship A6 Loan Withdrawals I need to see the value of D54 for each of the worksheets separately-similar to a linked cell reference, but I don't want to use a link- (I want to use Indirect.) I want to type in something like Worksheet1 or IndexSheet3, IndexSheet4 Maybe I need to move my indirect reference in the formula, I'm not sure... (I'm too chicken to try VBA- so I'm stuck with weird formulas in cells...) Thanks for all your help! -- JVLOLLAR ------------------------------------------------------------------------ JVLOLLAR's Profile: http://www.excelforum.com/member.php...o&userid=23721 View this thread: http://www.excelforum.com/showthread...hreadid=374087 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|