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?
-
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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!