How to create absolute cell reference in sheet summary

Options

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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/09/20 Answer ✓
    Options

    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))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/09/20 Answer ✓
    Options

    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))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Karen Webber
    Options

    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
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Excellent @Karen_B 

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!