No announcement yet.

Reference Current Cell Row In Array Formula

  • Filter
  • Time
  • Show
Clear All
new posts

  • Reference Current Cell Row In Array Formula

    Problem: I'm desperate to know why this doesn't work. I can not use ROW() in an array formula effectively.

    Goal: I'm creating an array formula that takes the value of a header cell above it. However the header cells row and column may very and the space between the cell and header may vary. The header location is only defined by the fact that there is a cell to the left of it in Column "P" that contains "Material & Hardware" and the header is always above the cell referencing it.
    Not only that but a copy of the the Header cell and cell referencing it can be found bellow and above the current Header and reference cell.

    What I've done: To locate the value of the header cell I'm using a MAX(ROW(1Current Cell Row -1))*("Materials & Hardware"=PCurrent Cell Row -1)) to give me the exact Row of my Header. But no matter what I can't make this work because the array formula will not Reference it's own holding cells Row location. I've tried:

    But the only thing that works atm is referencing a cell right next to the current one with ROW()-1 in it. For my next move this is not an option

  • #2
    Re: Reference Current Cell Row In Array Formula

    Try doing the following:
    1. In the current worksheet, select cell A1 (this is important!)
    2. Open Name Manager (Ctl+F3)
    3. Click New...
    4. Enter "THIS" into the Name: box (without the quotes)
    5. Enter the following formula into the Refers To: Box =!A1 (make sure cell A1 was selected when you started this!)
    6. Under Scope: select Workbook.
    7. Click OK and close the Name Manager
    8. Select all your headers give the selected range a defined name (in the box to the left of where you enter formulas or text, replace the cell reference in there with "Headers", no quotes)

    The following formula, in a cell selected in the dataset below the header row, will then give you the contents of the header row cell above the selected cell.


    I have not tried using this within an array formula but it might be worth a shot!
    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.


    • #3
      Re: Reference Current Cell Row In Array Formula

      Thank you for the response but the solution is not gonna cut it because there are headers next to each other (side by side) and they can be deleted or added. This would mess with the named range.
      Not only that I would have to define a new range for each level of headers (above and bellow) and change the code for each individual cell referring to them.

      All I need is something that returns the Column and/or Row number of the cell holding an array formula to use in that formula. Anymore Idea's?? I'm okay with VBA solutions


      • #4
        Re: Reference Current Cell Row In Array Formula

        I've now tried:
        - ROW(OFFSET(INDIRECT("R[-1]C[1]",0),0,0,1,1))
        Still no luck. It seems like where I already had an "Indirect" reference I can simply change to R1C1 format and use R[-1]C[1] and It works but I can't use indirect effectively anywhere else


        • #5
          Re: Reference Current Cell Row In Array Formula

          I FIGURED IT OUT!!!!
          So it turns out sometimes when you use "ROW()" in an array formula rather then returning a value it returns an array. In this instance what I did was I replaced my ROW() and COLUMN() values with names "AROW" & "ACOL" where:
          And just like that it converts the array mistake to a singular value. thank you for your help


          • #6

            This solution save my life !!! CapitalBlue, you solve someting very hard to figure out. I could tell the row() function returned an array by the {} around the value while looking at it from "Evaluate Formula". But I didn't know how to turn the value out of an array. Your solution is simple an elegant. Tank you for sharring.