Forum Discussion

Francesco Carmellini's avatar
Francesco Carmellini
Copper Contributor
Sep 27, 2018

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 ...
Formulas and Functions
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Sep 27, 2018

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.

 

  • LGM721's avatar
    LGM721
    Copper Contributor
    Dec 20, 2018

    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.

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 21, 2018

      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

Resources