Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with Absolute Values in Cells (Excel 2000)

    I am making 2 workbooks talk to each other but do not want the values to come back as absolutes so that I can autofill without having to delete the "$" marks. Is there any way to set a workbook or excel in general to default to "not absolute" all values?

    Thanks!

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Working with Absolute Values in Cells (Excel 2000)

    Not sure that I understand the question, but before you autofill, try selecting the entire formuls and press F4. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Absolute Values in Cells (Excel 2000)

    Let me re-phrase. When I open a workbook, can I highlight the entire workbook and set it so that none of the formulas contain the absolute value "$"? And is there a master setting within Excel that would allow me to change a default setting so that whenever I open Excel, it defaults to non absolute values?

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Working with Absolute Values in Cells (Excel 2000)

    No, you really don't want to do that. You want to keep the absolutes, but you want them to refer to absolute locations in your new workbook instead of your old workbook. To do this, have both your new and old workbook open. Go to the old workbook and select with the worksheet tabs of all the sheets that you want to copy to your new workbook (click on the first, then Ctrl-click on the rest, be sure to include all of the sheets that you reference with absolutes). Then use the Edit | Move or Copy Sheet menu to copy the sheets to the new workbook. Be sure to check the checkbox, Create a copy. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Working with Absolute Values in Cells (Excel 2000)

    Are you using links to specific cells in the other WB which you then copy through a fill/copyoperation? As Sam stated, when you create the first formula, use the F4 key to turn the link relative.

    Alternatively, if there are a number of absolute formula links that pre-exist when one WB is opened and you want to copy them as relative, you can Find-&-Replace the $ sign in those formulas with nothing.

    There is no default to always have newly created linking formulas be relative; the default when referring to a second WB or Pasting a Link is absolute; you have to set them to relative when you create them.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Absolute Values in Cells (Excel 2000)

    If you like, you can use my add-in to add the various choices concerning the rel vs abs formula change to your right click menu. Open the uploaded file, save as an add-in, open Excel and under Tools/Add-Ins check the one that says "CustomCellBar". Good luck!
    Attached Files Attached Files

Posting Permissions

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