Hi all,
I've posted this question in the "Ask an Excel Question" forum also, so sorry if you come across it twice!
I'm making a master spreadsheet that needs to pull data from about 80 source sheets.
I want to put a forumula like this....
=SUM(IF((SHEETX!$D$20:$D$40="Defeasance")*(SHEETX!$H$20:$H$40="EUR"),SHEETX!$L$20:$L$36,0))
.....in every cell in a column, but in each cell I want to replace SHEETX with a different sheet name. A big problem is that when the source sheets were being made, they were made in the wrong order, so they cant be reffered to as Sheet1, Sheet 2 etc. I'd like C3 to be linked to AdaptPharma, C4 to AditiTechnologies and so on. The only order the source sheets are in is alphabetical order. If it would help, the name of the sheet I want to link to each cell in the column (Csomething) is the value currently in Asomething.
I'm very very VERY new to VBA and I've written this macro.....
Sub PleaseGod()
For Each MyCell In Range("C3:C80")
OriginalText = MyCell.FormulaArray
CellINeed = MyCell.Offset(0, -2).Value
NewText = Replace(OriginalText, "Sheet1", "CellINeed")
MyCell = NewText
Next MyCell
End Sub
.....but while it isnt triggering error messages, it isn't doing anything else either. The cells are unchanged after it appears to run. I'd love and cherish any help anyone is able to give!
Thanks a million
I've posted this question in the "Ask an Excel Question" forum also, so sorry if you come across it twice!
I'm making a master spreadsheet that needs to pull data from about 80 source sheets.
I want to put a forumula like this....
=SUM(IF((SHEETX!$D$20:$D$40="Defeasance")*(SHEETX!$H$20:$H$40="EUR"),SHEETX!$L$20:$L$36,0))
.....in every cell in a column, but in each cell I want to replace SHEETX with a different sheet name. A big problem is that when the source sheets were being made, they were made in the wrong order, so they cant be reffered to as Sheet1, Sheet 2 etc. I'd like C3 to be linked to AdaptPharma, C4 to AditiTechnologies and so on. The only order the source sheets are in is alphabetical order. If it would help, the name of the sheet I want to link to each cell in the column (Csomething) is the value currently in Asomething.
I'm very very VERY new to VBA and I've written this macro.....
Sub PleaseGod()
For Each MyCell In Range("C3:C80")
OriginalText = MyCell.FormulaArray
CellINeed = MyCell.Offset(0, -2).Value
NewText = Replace(OriginalText, "Sheet1", "CellINeed")
MyCell = NewText
Next MyCell
End Sub
.....but while it isnt triggering error messages, it isn't doing anything else either. The cells are unchanged after it appears to run. I'd love and cherish any help anyone is able to give!
Thanks a million