Formula to change the variable to link to other excel file

[Fanny18]

Hi All, need your help on the following :-

Every month i need to do the monthly report and link the cell to the source file for multiple page, about 100 pages. By referring to the sample below, for eg :

a) In Feb'24, i need to link to source file column C. For Mar'24, i need to link to column D. Is there anyway, i change the cell D1 in below, the formula will refer to the column that stated in cell "D1"?
b) If in May'24, i change my cell in D1 to $F, then all the formula will become "=[Source.xlsx]ABC!$F$2. How to use formula for this?

The above can be substitute for "Find & Replace" function.

Tq.

 

[bobaol]

maybe this works. change cell D1, then run this macro. cheers

Sub Macro3Write_formula_using_Text()
'''after setting up the spreadsheet cells,
'''this is all done using Record Macro, push F8 to see it run line by line
''i always like to begin in A1
Application.Goto Reference:="R1C1"

'go to F1 and write the first part in text
Application.Goto Reference:="R1C6"
Selection.FormulaR1C1 = "'=[source.xlsx]ABC!"

''go to G1, and put G1 equals to C1
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[-3]"

''go to H1 for the second part in text
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "'C$2"

''go to I1 and concatenate the text into a formula, but still text
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=RC[-3]&RC[-2]&RC[-1]"

''Copy cell I1 to J1, paste as values
Selection.Copy
Application.Goto Reference:="R1C10"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

''parse text into "General", so that the text becomes values. or in this case, the text becomes formulas
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Calculate

''cut and paste to B1
Selection.Cut
Application.Goto Reference:="R1C2"
ActiveSheet.Paste
Calculate
End Sub