The INDIRECT function converts a string into a cell reference. With examples, I will show how useful this can be in certain applications.
First, let us look at how INDIRECT works with the help of a few examples.
D3:D6 is an array of numbers. The values in B3:B6 will be used in the different INDIRECT examples.
B10 =SUM(INDIRECT(B5)) This is equivalent to =SUM(D3:D6)
B12 =SUM(INDIRECT(“D”&B3;&”:D”&B4;)) A more elaborate example of assembling a string from different sources to be converted by INDIRECT to a range reference. B3 and B4 contain the start and end rows respectively of the array wewish to SUM
B14 =INDIRECT(ADDRESS(3;4;4)) The ADDRESS function also can be combined with INDIRECT.ADDRESS takes row and column numbers as arguments – converting them to a string – just what INDIRECT needs.
B15 =SUM(INDIRECT(“Sheet”&B6;&”.”&B5;)) An indirect reference to another sheet in the same document.
The one major difference between OOo Calc and Excel with regard to the INDIRECT function is the handling of named ranges. Excel allows you to make an indirect reference to a named range. This is not the case with OOo Calc.