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

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

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

2. 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

3. ## Re: how to prevent a formula in a summary sheet from automatically updating when a row is addedto the referenced sheet

<krisp1950@gmail.com> wrote in message
>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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1