Welcome!

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?

Find more posts tagged with

Comments

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?

Rania Alshawwa

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

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

20Dutch20

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.

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Trending discussions

can I get automated reminders for a 30 day check in when dates in my start date column are different
I have a column titled "start date" for each of my employees. I would like a reminder sent to a manager at the 30-day mark after their hire date.
Need Help with Multiple Criteria
I need a formula that looks for the a specific person in the Ball In Court column, then look at specific status in the Comment Status column and then look at another column and if it is blank return the number. I am not sure on what this should look like for the last column.
Formula to check a box based on criteria
Hello , I am looking for help to write a formula that will check a box if certain criteria are met. I have students that are signing up for a class and they need the previous class or classes as prerequisites. for example i have class 1, class 2 , class 3 and class 4. each higher number class needs to have completed the…