RosebudExcelGuy
New Member
- Joined
- May 29, 2009
- Messages
- 21
For many years now, I have been using INDIRECT.EXT through MoreFunc to pull information from an external documents. I have just upgraded to Office 2016 (from 2007) and have discovered that my documents no longer work. So I need a replacement.
One of the big uses was in our budget. The salary information is in one workbook. Each salaried staff member has a separate tab in that workbook which serves as the basis of the employment contract. Relevant information is then pulled into the master budget.
I accomplished this with this formula:
=INDIRECT.EXT("'"&$F$1&A4&"'!$F$37")
The file name was placed in F1 and the staff member name in at the start of the row (A4). The formula would then resolve to something like 'F:\2018\[Salary Worksheets 2017.09.14.xlsx]John Smith'!$F$37 which put his monthly salary into this cell.
One benefit was that I could create several different scenarios and then test them in a meeting by just changing the file name. Also by needing the name in the master budget sheet to be typed in, it ensured I referenced the right sheet (since a typo created a REF error).
I haven't experimented yet with Get Data. That's my next step, but I don't think I can make it dynamic in the same way (referencing the names). I suspect the need to constantly refresh may be problematic, but that's just a gut reaction now.
Is there another function in 2016 that can dynamically pull in information? Or another add in that will work with 2016?
Mark
One of the big uses was in our budget. The salary information is in one workbook. Each salaried staff member has a separate tab in that workbook which serves as the basis of the employment contract. Relevant information is then pulled into the master budget.
I accomplished this with this formula:
=INDIRECT.EXT("'"&$F$1&A4&"'!$F$37")
The file name was placed in F1 and the staff member name in at the start of the row (A4). The formula would then resolve to something like 'F:\2018\[Salary Worksheets 2017.09.14.xlsx]John Smith'!$F$37 which put his monthly salary into this cell.
One benefit was that I could create several different scenarios and then test them in a meeting by just changing the file name. Also by needing the name in the master budget sheet to be typed in, it ensured I referenced the right sheet (since a typo created a REF error).
I haven't experimented yet with Get Data. That's my next step, but I don't think I can make it dynamic in the same way (referencing the names). I suspect the need to constantly refresh may be problematic, but that's just a gut reaction now.
Is there another function in 2016 that can dynamically pull in information? Or another add in that will work with 2016?
Mark
