How to reference a source workbook by having the title in the destination workbook?

Hi,
I want see if it's possible to take the name of the source workbook and put it in a cell in my destination workbook and then use that as the reference in formulas. The source document title includes a date that changes yearly, or in some instances changes entirely, and I want to keep the formulas in the destination workbook intact. I don't want to have to go to every instance where I have used the source workbook and change the title. Instead I would like to copy the source workbook title, paste it into the destination in the same cell and have that propogate the needed changes into the destination workbook formulas.

Any help is greatly appreciated.

Thanks

You will need to use the INDIRECT function, which allows you to build up a cell or range reference as a text string and then passes it to Excel as a reference that it can handle. However, it does not work with closed workbooks, so your source workbook will have to be open at the same time as your destination workbook. If that is not a problem, then give some examples of the formulae that you use now and then we can give you the equivalents using INDIRECT.

Hope this helps.

Pete

So a shortened version of the formula is:

=SUMIFS('[Source_Document.xlsx]Sheet1'!$K$6:$K$1048576,'[Source_Document.xlsx]Sheet1'!$B$6:$B$1048576,"=B*",'[Source_Document.xlsx]Sheet1'!$G$6:$G$1048576,">="&B14,'[Source_Document.xlsx]Sheet1'!$G$6:$G$1048576,"<="&C14)

I need to also access Sheet2 in a different location in the same destination workbook.

Thanks for the help.