indirect function returns zero

Question

Hi,

I am trying to write a formula that can be pasted into multiple spreadsheet with different layouts to the data ranges.

I want to build the data ranges by using values in A1 and B1 rather than continually editing the formula.

For example, in a particular spreadsheet the count for the data range for column H is 25

Therefore the cells contents are as follows:

A1 = H             

B1 = 25

C1 = INDIRECT(A1&B1)

Yet C1 Returns 0 and not H25. Why?

Thanks in anticipation.

Answer 1

=INDIRECT(A1&B1) is equivalent to =H25. This formula will return the value of cell H25; if this cell is empty, the formula will return 0.

If you want to return the text H25, simply use =A1&B1