Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    #value! (Excel 2003)

    Hi all,

    I'm trying to place a formula in a cell so to represent the value of a cell in another workbook on a network.

    ='P:Press1ZONING SA-2-SA-3- TH-2[SA3 Zoning weekly completion.xls]SA2 Press Template'!$C$44:$E$44

    I keep getting #VALUE! and in other cells the formula itself, is there some thing I should know before I do this, so that it works?

    Thanks,
    Darryl.

  2. #2
    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: #value! (Excel 2003)

    It looks like you are reading 3 cells, not 1. What happens if you only use 1 cell:
    ='P:Press1ZONING SA-2-SA-3- TH-2[SA3 Zoning weekly completion.xls]SA2 Press Template'!$C$44

    Steve

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

    Re: #value! (Excel 2003)

    If you see the formula itself, the cell may be formatted as text.
    Two good ways to create such formulas:

    1) Open the other workbook.
    Select the range of cells you want to refer to.
    Copy to the clipboard (Ctrl+C).
    Switch back to the workbook where you want the formulas.
    Select the upper left cell of the destination.
    Select Edit | Paste Special...
    Click Paste Link, then click OK.

    2) Open the other workbook.
    Switch back to the workbook where you want the formulas.
    Select the cells where you want the formulas.
    Type =
    Switch to the other workbook.
    Select the source range.
    Press Ctrl+Shift+Enter.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: #value! (Excel 2003)

    If you created this formula by typing an equal sign (=) and then using the mouse to click your way over to the target cell, then it's likely that your target was a group of three merged cells. Your formula will probably yield the expected result if you'll just delete the last six characters of your formula. ...Template"!$C$44<font color=red>:$E$44</font color=red>
    - Ricky

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: #value! (Excel 2003)

    Hello Hans

    In your second example, what does Ctrl+Shift+Enter do? Normally when I'm building a formula, I would just hit the Enter key.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: #value! (Excel 2003)

    It is for Array formulas

    Mr Pearson will explain Array Formulas <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: #value! (Excel 2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Hey Jezza Bear

    I'm aware of array formulas. But I didn't see where that would be useful in the original question in the thread. So, for a moment, I lost my mind and thought that it would do something different... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    - Ricky

  8. #8
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: #value! (Excel 2003)

    Thank you all,

    I'm sure the problem was with Merged cells, I made all the formats the same, and the problem went away.....

    Darryl.

Posting Permissions

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