ROW(), INDIRECT() & MID() functions - syntax and result (help)

[Francesco Carmellini]

Hello everybody!
My name’s FC, I’m 36 and I’m from Italy.
By "sifting" on-line I found solution to several of my questions, often just doing "copy & paste" and trying ... I wish I wouldn’t limit myself to this, so I’m looking for suggestion/ support about the functions in object, to go deeper and better understand their behavior.

ROW() function
Let’s suppose we have the following values: A1 = 1 and B1 = 5; if I write in D1 {= ROW(A1: B1)} and then I press F9 I get {1}, while if always in D1 I write {= ROW(1: 5)} pressing F9 I get {1.2.3.4.5 }.
pt. 1 - why? (what’s the difference)
pt. 2 - what makes ROW() acting this way (i.e. essentially create a vector)? What’s “behind” that?

INDIRECT() function
Let’s get back to the starting data above: A1 = 1 and B1 = 5; if I write in D1 {= INDIRECT (1: 5)} I get #REF !, while if I always write in D1 {= INDIRECT (A1 & ":" & B1)} by pressing F9 it appears " The formula is too long formulas should not be longer than 8192 characters "… what’s that?!?
But if I finally write in D1 {= ROW(INDIRECT (A1 & ":" & B1))} I get {1.2.3.4.5}.
the questions are always the same: why and what makes INDIRECT() acting this way (?).

MID() function
Last question, this time for simplicity let’s write in D1 the formula {= MID("BOB"; ROW(1: 9); 1)} that by pressing F9 returns {"B". "O". "B"."."."."."."."}; also in this case, why and what makes MID() acting this way?

thanks a lot everyone! 

[#2]

Francesco,

let me explain the combination of ROW() and INDIRECT().

First ROW() expects a single cell reference and spits out a single value. If you need an array of values you have use an array in the function.

ROW(A1) = 1

ROW(A1:A5) = {1.2.3.4.5}

ROW(1:5) = {1.2.3.4.5}

The column letters can be left out or changed to any other column letter.

If you want it dynamic and use two cells for the lower and upper values the formula would be:

ROW(INDIRECT(A1&":"&B1)) = {1.2.3.4.5}

Where INDIRECT() transforms a text in a cell/array reference.

And as for MID(), if you want more characters to extract from a text than the length of the text MID() is polite and returns a blank text value for the "non-existing" parts.

[#3]

thnx a lot Detlef

[#4]

Hi, i have a similar problem in my case i dont understand the indirect function too well.

If i put in 

A1=T

B1=2

And the indirect function in the cell

C3 = Indirect(A1)

The result should be C3 = 2, but all i have is a error #Ref

And it says that the celd is not valid

If you could help it would be great.

[#5]

Hi,

INDIRECT returns the reference specified in the text. If you take =INDIRECT("A1") that will be converted to =A1 and result is "T".

If =INDIRECT(A1) it takes the text from A1, converts to =T, that's wrong reference and result is #REF