Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing cell references (2003 SP2)

    Say I have two sheets in one workbook, A and B.

    I have created A so that it calculates stuff using values in B.

    Someone else creates a version of B in a different workbook, B2, that now I am required to use. The differences between B and B2 are that some rows are switched in order. The data in B were organized by divisions in alphabetical order but now they are in order of highest to lowest revenues.

    My first question is that, if I simply move B2 into my workbook, delete the original B, and rename B2 to B, the references in A are all messed up (REF error). Is there a way to avoid this?

    If I create B2 in my original workbook rather than in a different workbook, is there a way to change references to B to now-newly created B2?

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing cell references (2003 SP2)

    You can copy B2 into your workbook, then use Edit | Replace to change all references to B into references to B2 before removing sheet B:
    In the Find what box, enter B!
    In the Replace with box, enter B2!
    If the real name of one of the worksheets contains spaces, surround the name with single quotes, e.g. 'Sheet 37'!
    Click Replace All.
    However, since the layout is not exactly the same, you'll probably have to edit some formulas manually.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing cell references (2003 SP2)

    Can you use VLOOKUP (or some other lookup function) to find the values in sheet B based on some ID (possibly Division ID) so that no matter what order sheet B is in, sheet A will find the correct values?
    Legare Coleman

Posting Permissions

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