Reference linking to several worksheets automatically
Posted by Paul on July 26, 2001 8:45 AM
I have a question about linking data between different spreadsheets. I know how to get a cell in one spreadsheet to reference data in another spreadsheet. However, I want to automate that task for multiple spreadsheets.
For example let's say we have one spreadsheet called test.xls and another spreadsheet called 1.xls. I have data at A5 in 1.xls that I want to reference at A1 in test.xls. That much I can take care of. Now let's say I have data at A5 in 2.xls that I want to reference at A2 in test.xls, A5 in 3.xls refrenced in A3 in test.xls, etc. How can I automatically increment the file referenced in succeeding rows? I have about 900 files to reference, and instead of making the reference 900 times and then changing the file name, 900 times, is there a way to dynamincally change the file referenced given my file naming scheme.
I hope this makes sense. I was told it may be possible to do within a macro, but I have little experience with Excel macros.