Dear Smartsheet experts,
I created a Smartsheet that captures weekly figures per location (via a form). I then created a portal to show both a report of all figures, as well as just the newest figure per location. To make the latter widget work I did the following:
1) Created a sheet summary that figures out the latest date added ("Newest entry" summary field equals =MAX(Date:Date)) (this works fine));
2) Created a VLOOKUP function for each location that matches the latest date (found in latest date function above) and then pulls the related figure from the right column (ex: =VLOOKUP([Newest entry]#, $Date$1:[South Africa]$39, 2, false))
The above process works perfectly - until a new entry is added at the top (via a form). Then the VLOOKUP reference suddenly updates to "$Date$2:" (remainder stays unchanged) and since it doesn't include the newest line (added to the top) my widget comes back as a REF NOMATCH for all locations.
To my understanding using $ should keep it an absolute reference; can anyone shed some clarity on what I'm not doing right?
