This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Whatever Happened to the @@ Function?
Problem: Back in Lotus 1-2-3, there was an @@ function. If you used @@(A3), Lotus would go to A3. A3 was supposed to contain a valid cell reference. Say that A3 contained the text C5. The @@ function would then return the value from cell C5.
Strategy: In Excel, this is called the INDIRECT function. Here are a few examples of how it works.
In the simplest case, consider a formula of =INDIRECT(F2). Excel will go to F2 and use the cell address found there. In the following figure, the answer in F4 first looks to F2 then to C1.
Figure 504 F2 says to look at cell C1.
The cell reference in the INDIRECT can be calculated on the fly. In this example, the VLOOKUP points to a different worksheet based on the quarter number in column B. INDIRECT uses concatenation to build something that looks like a worksheet reference.
Figure 505 Q2!A1:B99 is calculated on the fly inside of the INDIRECT.