Absolute Cell Referencing for New Top Row on Another Sheet

I am having trouble trying to write a formula that absolute references a specific row (Row 1) on another sheet. The referenced sheet has a new row added by Zapier everyday. I am writing a formula that would reference Row 1 Column 1. In Excel for example it would be "ReferencedSheet!$A$1. I don't seem to be able to create absolute references in another sheet, therefore my referenced cell keeps changing when the new row is added everyday. Any ideas?

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are a few different ways to do this. Are you trying to drop this reference into another formula? Can you provide more detail as to how you are using this data?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi There, I have the same issue that Ronaldo is facing, my first row is being updated by a form, and I need to reference the value in the certain cell in the first row in my formula. But each time a row is added the reference moves, is there an easy way to handle this?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It depends on how exactly you are using the data and where the formula is. Can you provide your current formula?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Similar issue Here.

    My Form appends New Data to the Top Row.

    But when I try and link the top 5 rows to display in a bar graph on Dashboard, the Linked Cells Move Down the List with the old data.

    I want to display the 5 most recent data entry values (there are 12 fields in the row).

    How can I link the Dashboard to the TOP 5 Rows such that the data links do not move down the list with the old data.


    Thank you for any assistance.

  • Hi @20Dutch20

    What I would personally do in this instance is use two helper columns to generate the Row Number in a cell on the row:

    =MATCH([Auto Column]@row, [Auto Column]:[Auto Column], 0)

    You can hide both of these columns. Then you can create a Report that only brings in the rows with the numbers 1 - 5 in the Formula Column, and use the Report as the source for your chart.

    This will then automatically update as new rows added.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!