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
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