26

For example, in an expression, instead of writing the address A1, how can I write something like: A(B1) with B1 = 1.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Hoa Vu
  • 2,865
  • 4
  • 25
  • 33

3 Answers3

35

I think another way of explaining what INDIRECT does is this way:

It turns text into a range, if that range is valid.

E.g. If you have text A1, it'll reference to A1. If you have text C2:C100, you'll get this as range.

Now, one of the most common ways in excel to generate text in the form of ranges is to concatenate. So that if you concatenate A and 1 (CONCATENATE("A","1")), you get A1.

And you can use a reference in this concatentate. Let's say that cell B1 contains 1.

=CONCATENATE("A",B1)

gives the text A1.

Hence, to get the cell A1, you would be able to use:

=INDIRECT(CONCATENATE("A",B1))

Except that the CONCATENATE() function now is a bit long, but don't fret! You can use &:

=INDIRECT("A"&B1)

Works just as well.

If you have something more complex like you have C in A1 and 32 in B1, to refer to cell C32, you can do:

=INDIRECT(A1&B1)

Which gives =INDIRECT("C"&"32"), =INDIRECT("C32") and finally =C32

Jerry
  • 70,495
  • 13
  • 100
  • 144
  • To evaluate sum from A1 to A9, given that B1 contains 1 and B2 contains 9 do: `=SUM(INDIRECT(CONCATENATE("A",B1)),INDIRECT(CONCATENATE("A",B2))`. – Adobe Jul 06 '15 at 12:34
  • @Adobe Hmm, that actually will add A1 and A9 and *not* A1 **to** A9. In that case, I would find simpler to do `=SUM(INDIRECT("A"&B1&":A"&B2))`. – Jerry Jul 06 '15 at 12:49
  • 1
    Sorry: should have semicolon in the middle: `=SUM(INDIRECT(CONCATENATE("A",B1)):INDIRECT(CONCATENATE("A",B2))`. Your version is much simpler though. – Adobe Jul 06 '15 at 13:01
  • Why do you use strings and CONCATENATE, instead of ADDRESS()? That doesn't make sense to me. – Matthias Urlichs Feb 15 '16 at 10:21
  • @MatthiasUrlichs INDIRECT gives you a **reference** from text, ADDRESS gives you a **text** from given coordinates (only numbers). – Jerry Feb 15 '16 at 13:53
  • @Jerry I know that -- `ADDRESS(B1,1)` has the same value as `CONCATENATE("A",B1)` if B1 contains an integer. However, if cell B1 contains the value "A3" then the ADDRESS version will throw an error while the CONCATENATE version will access a random cell. Also ADDRESS lets you access columns indirectly. Also² one might assume that the spreadsheet is able to internally optimize INDIRECT(ADDRESS(…)) so that it doesn't have to create and discard a string every time you calculate a reference; presumably such is not done with INDIRECT(CONCATENATE(…)). – Matthias Urlichs Feb 15 '16 at 19:49
  • @MatthiasUrlichs Sorry, now I understand what you were trying to say. I was trying to keep to the OP as much as possible, that is, given a column reference and a row number stored in a cell, get the reference of the two combined. If I may stretch the original example, having XCQ and 1 in cell B1, I should get $XCQ$1 as reference result. It is trivial using the method I described, but not as straightforward with ADDRESS simply because you first need to convert XCQ into a number, 16319. Granted that's unlikely to happen, but it can get cumbersome if you have various column references to convert. – Jerry Feb 16 '16 at 06:43
6

`INDIRECT' is the function you need to use.

From the documentation:

Syntax:

INDIRECT(Ref; A1)

Ref represents a reference to a cell or an area (in text form) for which to return the contents.

A1 (optional) - if set to 0, the R1C1 notation is used. If this parameter is absent or set to another value than 0, the A1 notation is used.

Example

=INDIRECT(A1) equals 100 if A1 contains C108 as a reference and cell C108 contains a value of 100.

=SUM(INDIRECT("a1:" & ADDRESS(1;3))) totals the cells in the area of A1 up to the cell with the address defined by row 1 and column 3. This means that area A1:C1 is totaled.

SeanC
  • 15,695
  • 5
  • 45
  • 66
2

=OFFSET(B1, 0, 1)

OFFSET(reference, row offset, column offset, area height defaults to 1, area width defaults to 1)

Example 1. We have multiplication table and need to find the answer for some stupid reason with OFFSET.

   A   B  C  D  E  F
1      1  2  3  4  5
     ---------------
2  1 | 1  2  3  4  5
3  2 | 2  4  6  8 10
4  3 | 3  6  9 12 15
5  4 | 4  8 12 16 20
6  5 | 5 10 15 20 25

Let's say we need 2x4 =OFFSET(A1, 2, 4) or in this case switching the numbers works equally well =OFFSET(A1, 4, 2)

Example 2. Let's calculate sum of all the numbers in the previous multiplication table which are for 2 or larger. =SUM(OFFSET(A1, 2, 2, 4, 4))

mhv
  • 35
  • 4