Google Spreadsheet Post #1650
*Yogi Anand, D.Eng, P.E.* ANAND Enterprises LLC -*- Rochester Hills MI** www.energyefficientbuild.com. May-27-2014*
*post by Shaun Fordham: (https://productforums.google.com/forum/#!mydiscussions/docs/Bcdc7CzA-GY)*
*Help with array formula positioning*
*Hello, everyone!*

*I am needing some help re-positioning a function into another cell and still have it work. I will start with a screen shot and the syntax:*

*The function is currently in D3 and reads as follows:*

*=ArrayFormula(if(K3:K<>"","Received",if(J3:J<>"","ROES'd",if(I3:I<>"","Approved",if(H3:H<>"","Emailed",if(G3:G<>"","Arranged",if(F3:F<>"","Exported",if(E3:E<>"","Edited",))))))))*

*Essentially, if anything is filled into the blue columns (in this case, initials and dates), the D (purple) column will auto-populate with the most recent (right-most) step. This is an amazing function I got with the help from someone on this board. There is a slight hiccup, however.*

*As you can see, there are 2 frozen rows and 4 frozen columns. I am constantly sorting the rows in different ways. D3 (the cell with the function) currently has Oakleaf HS on the row. If I re-sort it, D3—along with the function—will appear elsewhere and any D cell above the Oakleaf row will have a blank cell because the array formula will not read those. This is a problem.*

*I would like there to be a way for this function to work regardless of how it's sorted, so ideally in the D2 (purple) cell. How can I rewrite this function to make that work? I've tried simply copy/pasting it, but it only pulls from a row down and it's out of alignment. Is there a way to keep this function in the frozen section so it still works whilst keeping the word STATUS in D2? Any help would be greatly appreciated. Thanks!*

*Shaun*

*---*

*Here is a mock spreadsheet I made. The same cells apply as the sceenshot.*

*---------------------------------------------------------------------------------------------------------------------------------------------------*