Excel: Dynamic links and cell reference

An issue with Excel is that you are not able to generate links that can dynamically increase, say I want to collate the final mark of every student (e.g. 100 of them) from a list of spreadsheets that they have saved. One way to do this is to have them save according to their class index number. If the class size is 100, we will have 100 files named 1.xlsx to 100.xlsx. However, in order to collate the result, it will be tedious as you will have to open up manually and record their marks. You can create a new spreadsheet that collate all the results by referencing. For instance, on the new spreadsheet, type this in the cell A1: =’C:\Users\teacher\Desktop\excel\[1.xlsx]Sheet1′!$C$20 (where C20 represents the cell showing the total mark that a particular student has obtained).

Well, that would make life easy, isn’t it? Unfortunately, this is where you would get stuck with a static file path. Try dragging it and Excel will not increase the file number (e.g. from 1.xlsx to 2.xlsx) automatically for you. The workaround is slightly complicated but gets the work done easily. This requires the use 2 functions: CONCATENATE() and INDIRECT(). First, create a column starting from cell A1 with 1 to 100. This will be used as a file name reference. Next, you will have to separate the file name (which is 1 to 100) with the rest of the file path. In this case, we will use the CONCATENATE() function so that the file name in the file path is a standalone number (referencing column A as mentioned earlier on) which will increase automatically. Using the previous file path example =’C:\Users\teacher\Desktop\excel\[1.xlsx]Sheet1′!$C$20,  we will apply the CONCATENATE() function on cell B1 so that it appears as =CONCATENATE(“‘C:\Users\teacher\Desktop\excel\[“,A1,”.xlsx]Sheet1’!$C$20″). Once you are done, cell B1 should look like ‘C:\Users\teacher\Desktop\excel\[1.xlsx]Sheet1’!$A$1. Drag it all the way to 100 so that you should be able to see the paths update accordingly.

We are not done yet though. What shown on each cell has become a String due to the use of CONCATENATE() function. Meaning they are simply a combination of text which makes sense to us, but not Excel. Thus the mark referencing will not occur. To resolve this, you will need to make use of INDIRECT(), which is a very useful function. What INDIRECT() function does is it will take a text string and convert it to a proper reference, thus allowing the spreadsheet to reference the final mark of the students. To do so, add the INDIRECT() function to the existing code: =INDIRECT(CONCATENATE(“‘C:\Users\teacher\Desktop\excel\[“,A1,”.xlsx]Sheet1’!$C$20″)) and re-drag it again. Now, the final mark of the students should be collated easily. One thing to note however, is that reference error may occur. To resolve this, just open up all the files, then head back to the spreadsheet where the marks are collated.

3 thoughts on “Excel: Dynamic links and cell reference

  1. =INDIRECT(“‘C:\Users\teacher\Desktop\excel\[“&rows($a$1:a1)&”.xlsx]Sheet1’!$C$20″))
    would suffice

  2. Thank you tohlz. This works for me however I note your final comment “One thing to note however, is that reference error ay occur. To resolve this, just open up all the files, then head back to the spreadsheet where the marks are collated.”

    I get REF errors unless all the workbooks I am referencing are open. IS there anyway around this since I will be unable to open 100+ workbooks each time I want to aggregate my data set ?


Leave a Reply

Your email address will not be published. Required fields are marked *