How to create absolute cell reference in sheet summary

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?

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!