I am working on a sheet to track OT cost, I have a total page that has all the teams and months(periods), and extracts data from report that are save as sheet with date format. Currently I have =sumifs('06.03.2024'!F:F,'06.03.2024'!A:A,'06.03.2024'!B:B,N3) I would like it so that the 06.03.2024 is replaced with the cell(G6) it is in this way I can just copy the formula over and not have to go and change the date in formula every time I do a update for the next period. looking for something like this sumifs('b$1'!$F:$F,'$b1'!$A:$A,'$b1'!$B:$B,$N3) next period sumifs('c$1'!$F$:F,'c$1'!$A:$A,'c$1'!$B:$B,N$3) and so on. I have attempted indirect but not working. I might be missing something. thanks in advance.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
sum formula, using cell to refer to sheet name.
- Thread starter NickYOW
- Start date
bobaol
Board Regular
- Joined
- Jun 3, 2002
- Messages
- 190
- Office Version
- 365
- 2003 or older
- Platform
- Windows
no, you cannot do what you're requesting. here are a couple of options for you to consider...
''option 1, find and replace. you can try using this macro
Option 2...
it's a bit more complicated than simply changing the cell and use that cell as a reference in a formula. What you can do is write the formula in pieces, concatenate the pieces so it looks like a formula, then copy, paste values. then the final step is to parse that value. once the value is parsed, it becomes a formula. here is a picture and the code. in this case, your "live" formula will be in R1 and R5. you can run the code by hitting F8, hitting F8 will allow you to see the code run line by line so you can see exactly what it is doing. This was all done using Record Macro, nothing written by hand, except for the comments. Cheers!
''option 1, find and replace. you can try using this macro
VBA Code:
Sub Macro5_find_replace()
''recorded Cells.Replace What:="06.03.2024", Replacement:="06.04.1989", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
''written with reference to cells G3, G5
Cells.Replace What:=Range("g3"), Replacement:=Range("g5"), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End SubOption 2...
it's a bit more complicated than simply changing the cell and use that cell as a reference in a formula. What you can do is write the formula in pieces, concatenate the pieces so it looks like a formula, then copy, paste values. then the final step is to parse that value. once the value is parsed, it becomes a formula. here is a picture and the code. in this case, your "live" formula will be in R1 and R5. you can run the code by hitting F8, hitting F8 will allow you to see the code run line by line so you can see exactly what it is doing. This was all done using Record Macro, nothing written by hand, except for the comments. Cheers!
VBA Code:
Sub Macro1___Design_formula()'
Application.Goto Reference:="R4C7"
Selection.FormulaR1C1 = "''manually enter into G5"
''begin designing the formula
Application.Goto Reference:="R5C9"
Selection.FormulaR1C1 = "'=sumifs('"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[-3]"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "''!F:F,'"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[-5]"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "''!A:A,'"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[-7]"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "''!B:B,N3)"
''concatenate the cells so it looks like a formula
Application.Goto Reference:="R5C17"
Selection.FormulaR1C1 = "=RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2]"
'''copy the formula, paste as values, so it is now working with values
Selection.Copy
Application.Goto Reference:="R5C18"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.Copy
''past to row1, as row1 needs to be NOT empty when parsing
Application.Goto Reference:="R1C18"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'parse into general format. parsing will convert the value into a formula since it has the equal sign in front
Application.Goto Reference:="R1C18"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End Sub
Last edited:
Upvote
0
dreid1011
Well-known Member
- Joined
- Jun 4, 2015
- Messages
- 3,085
- Office Version
- 365
- Platform
- Windows
Are you trying to put the sheet names in cells and reference those cells for the sheet names? That can be done with INDIRECT...
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C1:C3 | C1 | =INDIRECT("'"&A1&"'!A1") |
| D1 | D1 | ='06.03.2024'!A$1 |
| D2 | D2 | ='06.04.2024'!A$1 |
| D3 | D3 | ='06.05.2024'!A$1 |
Looks like they want to change the sheet names, which can be done... but I may be wrong.
Upvote
0
yes I want to have 12 sheets with dates but they will very, it might be 02.04.2024, 04.05.2024, .01.06.2024,(theses will the sheet names) so what ever is entered in the row 5 will be the reference to the sheet, and then from there the formula will reference the cell that is related to that sheet this way I don't have to manually enter the date(sheet name) it into every formula over and over. it refer to the sheet with that date, will find the team 601 and the period and return a number.
Upvote
0
