Macro filling only empty cells in range with formula referring to diff' file based on crit

Hello All,

I am trying to understand if it is possible for a macro to insert a formula* into a cell but only if that cell is empty [meaning not holding any values].

*formula would also need to determine to refer itself to a correct file.

I have attached a set of 4 Files:
1. Main File.xlsx [this is where I am working on a macro]
2. Core S4.xlsx
3. Core Bev.xlsx
4. Finishing.xlsx

the formula needs to be added to column "Q", being:

Please Login or Register  to view this content.

Therefore in the formula column "I" from Main File needs to determine what file to refer to.

I do remember that I have once worked on something where a formula could determine what tab to go to based on the value of a certain cell.

I know I could possibly do "if" formula for changing paths [file names] based on the criteria but there are few sections to check for and we are only limited to 5 "if's".

I understand how to determine a last row I want a range to be for but not a clue how to manipulate codes to do what is described above.

Does anyone think it is doable? Has anyone of you have ever done something like this before?

I appreciate any help.

Thank you in advance

Simon