Tuesday, May 27, 2014

yogi_Use A Computed Column Formula In Cell of A Frozen Row So The Range Can Be Sorted With Formula Working

                                         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:
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!
Here is a mock spreadsheet I made. The same cells apply as the sceenshot.