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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

krisp1950@gmail.com - 12 Jan 2006 19:49 GMT

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

Reply to this Message

SteveG - 12 Jan 2006 21:30 GMT

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

Signature

SteveG

Reply to this Message

kcc - 13 Jan 2006 02:33 GMT

>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.
[quoted text clipped - 13 lines]
> 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

Reply to this Message