Create formula to add cells from variable dynamic list of worksheets

[dcdc113]

Struggling to create a formula or VBA to add data from multiple sheets in another workbook based on selection from a dropdown list of worksheets.

Example:
Dropdown List
...documents\[workbook1.xlsx]Sheet1'!$B$3
...documents\[workbook1.xlsx]Sheet2'!$B$3
...documents\[workbook1.xlsx]Sheet3'!$B$3
...documents\[workbook1.xlsx]Sheet4'!$B$3

Say I want to add the values from Sheets 2 and 4, those are chosen from dropdown and sum of B3 in those 2 sheets are placed in D4.
The names of the workbooks and sheets will change and the list will change dynamically. I have tried to used concatenate as a formula in the cell and to use VBA to place the formula in the cell with no luck.


Thanks

 

[GlennUK]

When you use VBA to place the formula in the cell. what formula do you end up with? Debugging that seems a good place to start.