Absolute Cell Referencing for New Top Row on Another Sheet

Rolando Cooper

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?

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