How to create absolute cell reference in sheet summary

Karen Webber

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?

Bassam Khalil

Hi @Karen_B

Hope you are fine, please try to set the table range for VLOOKUP absolute to read any new row by changing your formula as following:

=VLOOKUP([Newest entry]@row, [Date]:[South Africa], 2, false))

Bassam Khalil

Hi @Karen_B

Hope you are fine, please try to set the table range for VLOOKUP absolute to read any new row by changing your formula as following:

=VLOOKUP([Newest entry]@row, [Date]:[South Africa], 2, false))

Karen Webber

Thank you very much Bassam, I had to remove the "@row" reference as for some reason that made the function unparseable, but with the table range reference it made all the difference.

Bassam Khalil

Excellent @Karen_B