Pull formula with dynamic workbook name

Hello all,

I am trying to use a basic pull formula: =IF('C:\Projects\[WorkbookName.xlsx]Sheet1'!A1="","",'C:\Projects\[WorkbookName.xlsx]Sheet1'!A1) where WorkbookName can be pulled from a separate cell. So for example if A1 is the previous formula and A2 is WorkbookName2, the formula would read: =IF('C:\Projects\[WorkbookName2.xlsx]Sheet1'!A1="","",'C:\Projects\[WorkbookName2.xlsx]Sheet1'!A1) and pull from the WorkbookName2. I've been attempting a mixed IF and INDIRECT formula, however it has not produced results.

Thank you for any help in advance. I would prefer non-VBA answers.