Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Correct (Excel 2003)

    Hi,
    I need to type some text again and again. Most part of this text remains the same, except that cell references keep changing. So I used auto correct formula like this:
    Replace : ch
    With: = "Changed Rem. Hrs. from " &P105& "h" &"to" &L105&"h"
    wherein I want cells P105 and L 105 to change their referneces relatively whenver I type "ch" in new cell. However the formula that I typed always references cells P105 and L105. Is there any way out?

    Thanks in Advance

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

    Re: Auto Correct (Excel 2003)

    AutoCorrect doesn't really store a formula, it stores literal text. So wherever you type 'ch', the same text is inserted as a formula.
    You can use a named formula instead:
    - Select the cell from which the relative reference is correct.
    - Select Insert | Name | Define...
    - Type ch (or whatever name you prefer) in the name box.
    - Type the formula in the Refers to box:

    ="Changed Rem. Hrs. from "&P105&"h to "&L105&"h"

    - If you want the column references to be fixed, use $P105 and $L105.
    - Click OK.
    - You can now enter =ch in a cell.

  3. #3
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Correct (Excel 2003)

    Thanks Hans. It works!

  4. #4
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Correct (Excel 2003)

    Hi

    One more question. Is it possible to share names across different workbooks?

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

    Re: Auto Correct (Excel 2003)

    I don't think so.

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

    Re: Auto Correct (Excel 2003)

    You could create a macro in your personal macro workbook Personal.xls:
    <code>
    Sub InsertFormula()
    ActiveCell.FormulaR1C1 = "=""Changed Rem. Hrs. from ""&RC16&" & _
    """h to ""&RC12&""h"""
    End Sub
    </code>
    (You may have to tweak the actual code)
    It will then be available in all workbooks. You can assign the macro to a custom keyboard shortcut and/or custom toolbar button.

    See Legare Coleman's <!post=Personal.xls Tutorial (All), 118382>Personal.xls Tutorial (All)<!/post> if you need info about creating and editing your personal macro workbook.

Posting Permissions

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