I have a co-worker who is an Excel wizard. He set up a system for automatically gathering data using references to other tables. I’ve learned a lot from him like how to use lookup tables, filters, reference other files, etc. Microsoft lists out the syntax for referring to other sheets.

There was one one instance where we needed the tab name to vary based on another cell. We’ve been doing this by hand each time we add a new tab. It’s not hard, but it is boring and tedious. Being a good computer programmer, I want the computer to do tedious for me. The problem is that it doesn’t take long enough to update it by hand so I can never justify looking into it.

I recently installed Office on my Windows 8 VM because I needed to see how something looked in real Word. (vs Open Office.) Since I now have Excel, I decided to try it out. I got it working much faster than I thought.

Just like programming, I did iterations to build up to it.

Iteration 1 – sum another tab

I created file1.xlsx with two tabs: tab1 and tab2. In each, I added some numbers to the first column of each. I then wrote the formula to add them up:

=SUM([file1.xlsx]tab1!$A:$A)

Explaining what this means:

• [file1.xlsx] – refer to another file
• tab1 – refer to this tab in that file
• !$A;$A – all columns in column A. The $ means to always use column A even if I copy paste the formula into multiple rows.

Iteration 2 – try to use a formula based on a String

=SUM(INDIRECT(“[file1.xlsx]tab1!$A:$A”))

• INDIRECT – the indirect function lets you pass text to refer to an Excel reference

Iteration 3 – append strings to build the expression

=SUM(INDIRECT(“[file1.xlsx]tab”& A3 &”!$A:$A”))

• & – concatenate strings