Insert Row Breaks Formula's - Seeking Work Around

[maximillianrg]

Hello Excel Masters - Seeking your help on the following:

I have a workbook with two tabs, sheet1 and sheet2

Sheet1 cell A1 = 5
Sheet2 cell A1 contains the formula =Sheet1!A1 which = 5

If I select sheet1 row 1 and insert a new row so that 5 is now in cell A2, the formula in Sheet2 cell A1 auto updates to =Sheet1!A2

is it possible to create a formula in Sheet2 cell A1 that always pulls the value from Sheet1 cell A1 regardless of how many insert rows are done above it?

 

[Fluff]

How about

=INDEX(Sheet1!A:A,1)

 

[GSP75]

Yes, it is possible to create a formula in Sheet2 cell A1 that always pulls the value from Sheet1 cell A1, regardless of how many insert rows are done above it. You can use an absolute reference for the cell in Sheet1.

To do this, change the formula in Sheet2 cell A1 to =Sheet1!$A$1. The dollar signs before the row and column references make them absolute references. This means that the formula will always refer to cell A1 in Sheet1, no matter where it is copied or how many rows are inserted above it.

Alternatively, you can also use the INDIRECT function in the formula to reference Sheet1 cell A1. The formula in Sheet2 cell A1 would be =INDIRECT("Sheet1!A1"). This formula works by constructing a text string that represents the reference to cell A1 in Sheet1, and then using the INDIRECT function to convert that text string to a valid reference.

 

[Fluff]

The dollar signs before the row and column references make them absolute references. This means that the formula will always refer to cell A1 in Sheet1, no matter where it is copied or how many rows are inserted above it.

I have you actually tried that?

Alternatively, you can also use the INDIRECT function

Why use a volatile function when there is no need?

 

[maximillianrg]

More Context - I have an IFTTT script that I cannot modify. It pulls data from a website, locates the first empty row in an excel sheet, Inserts a row, and then adds data to the row. I then have a series of formula that clean up the data and the cleaned up data is then pulled from another sheet via a vlookup

I believe the index command is going to work - Testing now

 

[GSP75]

How about

=INDEX(Sheet1!A:A,1)

as well