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
-
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!
-
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?
-
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!
-
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:
- An Auto-Number column to generate a unique ID per row
- A Text/Number column with a MATCH formula to find the row number, which adjusts as rows are added
=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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!