Forum Discussion
ROW(), INDIRECT() & MID() functions - syntax and result (help)
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 ...
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.
thnx a lot Detlef