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
-
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.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
-
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.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"
-
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.
-
Excellent @Karen_B
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!