+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    krisp1950@gmail.com
    Guest

    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. #2
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    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. #3
    kcc
    Guest

    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
    news:1137095344.278535.65070@g47g2000cwa.googlegroups.com...
    >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



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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