title: Setting formula range from first to last populated cell in a column?
body: <p>For a league I run we keep track of games played and w/l/t and calculate that into a ranking score.  The player name is listed in column U and the ranking score in column AD of a fixed table.  I then use an array formula to list the players in ranking order in column E (then vlookup to pull in other stats based on the value in E for that row).  Specifically I use this formula in column E:</p>
  
  <p><code>=ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX($U$4:$U$153,MATCH(LARGE($AD$4:$AD$153-ROW($AD$4:$AD$153)/COUNT($AD$4:$AD$153),ROW(E72)-ROW(E$4)+1),$AD$4:$AD$153-ROW($AD$4:$AD$153)/COUNT($AD$4:$AD$153),0))), 1, 1)</code></p>
  
  <p>I need to be able to add players to the table in U:AD without having to edit the formula every time, i.e. from $U4:$U153 and $AD$4:$AD$153 to $U4:$U154 and $AD$4:$AD$154 in all the various places in the formula then copy the new formula all the way down.</p>
  
  <p>Is there a way that I could define the range as $U$4:$U(last populated row) and the same for column AD in the above formula?</p>
  
  <p>I eventually be using this in both Excel and Google Sheets so I would really like to avoid scripting. First I'm looking to solve this for Google Sheets.</p>
  
  <p><a href="https://docs.google.com/spreadsheets/d/1CopCzSR7PaU6CZFBXEzocrIYy3CytklnTvuWTyhaxXk/edit?usp=sharing" rel="nofollow noreferrer">Here is a copy of the sheet</a> I am working on.</p>