Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Names in formulas (Excel 2003)

    hello all,
    i have a severe problem with the use of names in formulas, for example in the planning of resources ->
    =NETWORKDAYS(AC10;AD10;KEX) - (KEX is the UIDof a colleague, here taken for his own calendar of absence).
    If i want to make a reference to the cell, where his UID is, this seems to be impossible to handle for Excel, i only get
    the error #value!.... Does anyone know, how to handle this mix (names with references)??
    stefan

  2. #2
    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: Names in formulas (Excel 2003)

    Hi Stefan

    The first thing that jumps out at me is the syntax of your formula but maybe down to the layouts of our keyboards, I should see it as:

    =NETWORKDAYS(AC10,AD10,KEX)

    Where I use commas and not semi-colons
    Jerry

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names in formulas (Excel 2003)

    You are right, here in austria we use ; instead of , .. thats not the problem.
    sorry, that i copied the formula from the cell, without thinking of the readers of my
    post..
    mea maxima culpa

  4. #4
    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: Names in formulas (Excel 2003)

    Cool

    I was just checking <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I have recreated the formula in my Excel and I seem to have got it working!!

    Find attached my excel item where I named the range Y1:Y5 as KEX

    Alternative you may find that the dates are stored as text so you will have to convert them using the DateValue function i.e.

    =NETWORKDAYS(DATEVALUE(AC10),DATEVALUE(AD10),KEX)
    Jerry

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names in formulas (Excel 2003)

    well, it still works in my sheet, but i guess, i did not make it clear enough, i want the array of colleagues
    to be variable, but this formula is not. What i am searching for is a solution for making the reference to the text "KEX "(for example)
    flexible....
    but thanks, jerry...

  6. #6
    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: Names in formulas (Excel 2003)

    You can define the name formula "Kex" as variable (using indirect, offset, etc.)

    You will have to elaborate on how you want it to change and what you want the range to refer to.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names in formulas (Excel 2003)

    that is the problem, exactly....
    see attachment
    Attached Files Attached Files

  8. #8
    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: Names in formulas (Excel 2003)

    The problem appears to be that all the values are not dates which causes the error, it is not the name...

    If you make all the values dates, then the error goes away...

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names in formulas (Excel 2003)

    even if i change the textdates into dates/values (in the calendar), excel changes them from time to time, but this seems
    not to be the problem, the formula works with it. I just cant manage to replace KEX with offset or indirect.

  10. #10
    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: Names in formulas (Excel 2003)

    The one you attached does not work for me: it has the error as soon as I calculate the workbook. It does not display an error as posted since it has not been calculated.

    If I make the text values dates, I can use something like:
    =NETWORKDAYS(I8,J8,INDIRECT(E8))

    And it works fine. The solution is to make sure all the values in the range are numbers. The text-values give the errors in the formula.

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names in formulas (Excel 2003)

    steve, this saves my day today. thak you,
    stefan

Posting Permissions

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