Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Curious results from Database Functions (Excel 2000)

    I have included a zip file that shows the issue. It contains two files. Test3.xls has a function in a module to make the DGET formula more generic. 74060a.xls has trivial sample of data.

    The example is using DGet, but the same behavior is found with all the D functions.

    I am trying to make use of D formulae using dynamic input. By dynamic input I mean that the file references are dependent upon some formula, the value searched for is based on a formula, etc.

    Array formulae have some of the same difficulties, and simple vlookups are not always robust enough.

    Any ideas?

    Unfortunately I cannot remove the checkmark for transition formula evaluation in the spreadsheet I need this capability. Remove it for Test3.xls and you will see what I mean.

    Also, how come I have to hit Ctrl-Alt-F9 to get my custom function to recalc?

    Thanks in advance
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curious results from Database Functions (Excel 2000)

    I'm not sure what your question is. Your function seems to work. I did get #Value in the functions referencing A4:A5. That cleared up when I copied A8:A9 and pasted to A4:A5 (bizzare). If this isn't the problem please post again.

    As for having to use crl+alt+F9, just include the line
    Application.Volatile
    at the beginning of your function then edit the functions in your spreadsheet and they will update properly.

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curious results from Database Functions (Excel 2000)

    That is the problem. When the cell is text it works, but when it is a formula pointing to text it doesn't work.

    I specifically chose that as the simplest instance of the problem. Strange isn't it?

    Thanks

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curious results from Database Functions (Excel 2000)

    You will not like this, but I would suggest to uncheck the transition formula evaluation and rebuild all formula's so they do the same as when the transition formula evaluation is checked. You are forcing Excel to mimick Lotus, which means Excel is not doing things the way it is used to or is programmed for. I guess to have reliable results it would be best to use Excel functionality in Excel. Especially if this workbook is to be used in future. Also, not many people have experience in what XL does exactly when transition formula evaluation is set (including me), so you'll get little help.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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