VLOOKUP Formula for Dashboard Updates

Susan@SMC
Susan@SMC
edited 12/09/19 in Smartsheet Basics

Hi there,

I have a dashboard widget and I'd like it to automatically update every time a row of data is added to the a SmartSheet (see attached screenshot).  I'm trying to use a VLOOKUP formula on the top row to always pull the latest data, but it's not working.

Any formula wizards out there that can help?

Thanks!

Susan

sheet.png

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    There are a lot of different ways to approach this. The best way that should be the hardest to break is to add a system column, I recommend date created. This will have the date and time the row is initialized.then you can use an index match

    lets say the column is called "Created"

     

    =index([Amount of all payments]:[amount of all payments],match(max(Created:Created),Created:Created,0))

    this should give you the desired result. if you want you can hide the created column afterwards and its like it never existed in the first place, it also doesn't add any stress to the sheet as it is already calculated & tracked anyways.

    I recommend this because the time is included, and if you have multiple submissions in a single day, it will grab the most recent. Even if the rows get mixed up you don't have to worry about it as the date/time is row based and uneditable, so it should always be correct.