how to prevent a formula in a summary sheet from automatically updating when a row is addedto the re

[krisp1950]

I have a workbook set up that consists of several sheets and a summary
sheet that refers to the third row on every other sheet.

The worksheets that are referenced contain dated information that is
sorted with the most recent entries entered in row 3 and these are the
entries I need to track in the summary sheet.

When I insert a new row 3 in these worksheets, the formula in the
summary automatically updates to line 4 and I need it to always
reference row 3, the newest entry.

This should be simple but I cannot find the solution.

Any ideas?

Thanks,
krisp1950

 

[SteveG]

You could use the INDIRECT function. For example, if you are referring
to Sheet2!A1 then,

=INDIRECT("Sheet2!A1") (this is for A1-style) or

=INDIRECT("Sheet2!A1",FALSE) (this is for R1C1-style)

HTH

Steve

 

[kcc]

I have a workbook set up that consists of several sheets and a summary
sheet that refers to the third row on every other sheet.

The worksheets that are referenced contain dated information that is
sorted with the most recent entries entered in row 3 and these are the
entries I need to track in the summary sheet.

When I insert a new row 3 in these worksheets, the formula in the
summary automatically updates to line 4 and I need it to always
reference row 3, the newest entry.

This should be simple but I cannot find the solution.

Any ideas?

Thanks,
krisp1950

These 2 work as long as you don't insert a row before row 1.
=INDEX(Sheet2!A1:A3,3)
=OFFSET(Sheet2!A1,2,0)
The Index range will grow as rows are inserted but you will get the 3rd row.
Offset is a little cleaner, but has 2 issues that bug me. The first is that
if
you trace dependence from Sheet2!A3 it will not know that the offset
uses it. Also offset will fail if it refers to a cell in a closed file.
kcc