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<>"","
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.
---------------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment