Welcome Guest
Log In | Register )
You last visited December 3, 2016, 6:52 pm
All times shown are
Eastern Time (GMT-5:00)

@DGET function

Topic closed. 6 replies. Last post 8 years ago by KnuckleHead.

Page 1 of 1
PrintE-mailLink
KnuckleHead's avatar - box

United States
Member #73037
April 3, 2009
147 Posts
Offline
Posted: April 23, 2009, 10:02 am - IP Logged

Good morning all,

I am having a problem getting this to work.

Iwant to be able to enter winning numbers into my sheet, then have the@DGET formula in the cell next to the numbers get the pattern numberfrom a table on another sheet. Below is what I have that's not working.

@DGET($DATA="NUM1"#and#"NUM2"#and#"NUM3","INDEX",$CRIT="NUM1"#AND#"NUM2"#AND#"NUM3")


This Range is named "CRIT". [This is the sheet that I enter "winning" numbers into.]

INDEX     Num1     Num2     Num3

  ERR          5             6           7


This Range is named "DATA". [It is my "Pattern" sheet.]

INDEX     Num1     Num2     Num3

    1            0             0           6

    2            4             5           6

    3            5             6           7

    4            7             6           6


"ERR" is where the @DGET function is located and should be the answer 3.

I have setup the "pattern" table with "Labels" across the top and "General" throughout the remainder. Can anyone tell me why this formula will not work? I'm sure it has to be the way that I have the "=" (compare) written.

My spreadsheet program is Lotus123, although I understand that Lotus123 and Excel work very simular to each other.

I'd be greatful for any help and directions.

    Curious2k3's avatar - Curious 2k3.jpg
    Michigan
    United States
    Member #34209
    March 1, 2006
    265 Posts
    Offline
    Posted: April 23, 2009, 11:22 am - IP Logged

    It may be a problem for not defining where it needs to place it. Perhaps try the following.

     

    =DGet(A1:D1, "Ball One", A1:A2) and so on. Try defining the name.

      KnuckleHead's avatar - box

      United States
      Member #73037
      April 3, 2009
      147 Posts
      Offline
      Posted: April 24, 2009, 9:36 am - IP Logged

      Curious2k3,

      Thanks, tried that, didn't work. It appears to be something to do with telling the formula to "match" these 3 cells for the other 3 cells. I haven't figured out how the tell the formula to "match". I'll keep trying though.

        Curious2k3's avatar - Curious 2k3.jpg
        Michigan
        United States
        Member #34209
        March 1, 2006
        265 Posts
        Offline
        Posted: April 24, 2009, 2:54 pm - IP Logged

        Hmm. I do know that DGET will return errors if multiple matches are found. You may need to look into an array, using Index and Match.

          KnuckleHead's avatar - box

          United States
          Member #73037
          April 3, 2009
          147 Posts
          Offline
          Posted: April 26, 2009, 11:09 pm - IP Logged

          Hmm. I do know that DGET will return errors if multiple matches are found. You may need to look into an array, using Index and Match.

          I searched thru 1000 different rows (patterns) and did not discover another of the same pattern. What I did discover was that my "pattern" numbers are 1 number above the drawn number, so I'll just enter the "pattern" number as 1 number above the "drawn number. Less automation, but it should work.

           

          Thanks for the help.

            Avatar

            United States
            Member #41846
            June 23, 2006
            458 Posts
            Offline
            Posted: April 27, 2009, 8:59 pm - IP Logged

            I can not make the DGET command work in excel per your example. I have checked many places that explain how things work, they all seem to be copies of the original.  I think there is a small detail missing somewhere or this command is no longer supported maybe??? I am using 2007 version.

            good luck

            p8

              KnuckleHead's avatar - box

              United States
              Member #73037
              April 3, 2009
              147 Posts
              Offline
              Posted: April 28, 2009, 9:15 pm - IP Logged

              I can not make the DGET command work in excel per your example. I have checked many places that explain how things work, they all seem to be copies of the original.  I think there is a small detail missing somewhere or this command is no longer supported maybe??? I am using 2007 version.

              good luck

              p8

              Thanks phileight,

              I use an older version of Lotus123. It works great. I just need to figure out how to write formulas better. The program is loaded with lots of help, I really need to have a better understanding of everything it can do. I've written some macros and  recorded some very simple scripts, but now I want to go further and create scripts. That's where I get lost quick. I don't comprenhend computer language, so it's taking me a lot longer to figure things out. I've been reading a lot of PDF files about mathematics and what they do, again, I'm lost just trying to understand it all. Math wasn't my best subject in school (and that was a long time ago). So, I figure to learn, I need to read. The more PDF files I can find with examples, the easier it is to understand what I'm looking at, then I'll need to figure out how to write a script that does what I want it to do. Ooh, what was that drip, never mind, just my brains melting out of my ear again...LOL (guess I need to hold my head straight).

              Thanks for taking the time to look into this though... I'll keep reading and maybe I just might learn something useful...