Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling in other sheets (2002 SP3)

    I have one sheet (called the Data sheet) on which I input everything for a particular workbook. When I delete a ROW in the Data Sheet, is there some way to set the other sheets to do that as well so I don't have to go through and look for the "REF" cells?

    Basically it's a phone book workbook. The data sheet contains all the info, the other sheets are formatted in different ways, sizes, by first name, last name, phone number etc.

    Thanks.
    Pooja

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Filling in other sheets (2002 SP3)

    <P ID="edit" class=small>(Edited by macropod on 19-Oct-05 13:10. correction/expansion of OFFSET syntax)</P>Hi Pooja,

    You could use either an INDIRECT or OFFSET function to avoid the REF errors. For example:
    =INDIRECT("Sheet1!A1")
    or
    =OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1) or =OFFSET(Sheet1!A1,,)
    will all refer to whatever is now in Sheet1 A1, regardless of any intervening row/column insertions or deletions.

    The OFFSET function, though slightly more complex, is usually easier to implement since you don't have to hard-code each cell reference - you can simply use copy/paste after setting up the first offset reference for each block of data.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filling in other sheets (2002 SP3)

    You could use a named range.

    Select Data Sheet cell A1

    Insert - name -define
    Name: GetMe
    Refers to: =IF(ISBLANK('Data Sheet'!A1),"",'Data Sheet'!A1)
    [note that A1 has no dollar signs ($)
    <ok>

    Then in the other sheets just enter in a cell:
    =GetMe
    and it will "extract" the cell with that address from the Data Sheet. If you delete the rows you will no longer get the errors

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in other sheets (2002 SP3)

    Thank you both!

Posting Permissions

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