I have a 'master' worksheet, which contains a list of active workbooks. Each row on the master contains several data elements from each of the active workbooks:
File#, Date, Status, Customer, Etc.
This is accomplished by copying a row of data in the active workbook and pasting as a link on the master.
When an active workbook data element is changed (date, or status, for example), the link on the master changes as well. Then the active workbook is closed until needed again.
The linked data on the master allows for an overview of active files, and for sorting based on certain criteria, such as the next date to action the file.
What I now wish to do is eliminate the process of copying & pasting; this would be done, in theory, by putting a formula in each cell of the master that references a variable: the file number, located in column A.
Thus, where the pasted link formula reads:
='C:\Active Files\[4545.xls]Home'!$O$1
The desired theoretical formula would read:
='"'C:\Active Files\["&$A25&".xls]Home'!$O$1"
However, as you would already know, Excel does not permit a variable file name in this type of formula.
The first solution is the INDIRECT function, but because the workbooks are closed after the relevant fields are updated, and the master is frequently re-calced, this invalidates the goal of visible summary data with less work.
The next solution would be the "morefunc" add-in and its' INDIRECT.EXT function; this actually is a perfect solution, but for one small problem: it is unstable, and causes Excel to crash. Without blaming Laurent for his work, it does seem to be a widespread problem with the add-in that is not fixable by the lay-user. Maybe he could just give up the code for this particular function.
I've kicked around a couple of the VBA codes offered up by some of the pro's, but repeatedly run in to problems with making them fit my particular need. The reason for that is that I am learning VBA but am still a long way from understanding it.
To summarize, I am looking for a formula or code that will allow me to link data from external workbooks with a variable file name. This would permit me to copy the formula down the rows, eliminating the need to copy/paste the links one at a time. It does not have to access the data from the workbook when it is closed, as the change will always happen when it is open, but it cannot lose the data when the workbook is closed (as INDIRECT does.)
I look forward to getting some feedback on this, and will gladly provide whatever additional information may be needed to help resolve this (minor) dilemna.
TIA
Bob
PS - Very generic examples were attempted to be attached, but the master sample failed to upload repeatedly, so I regret that I cannot give samples to look at.
