Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formulas from linked db (9.0/2001)

    I have a Excel Worksheet that is linked to a querry in an Access DB, so everytime I update the DB, it reflects in Excel. I'm trying to create a formula based off that link so I can in turn create a chart. The fields I have linked are labeled: Planned start (m-yyyy format) & original (from the querry, makes all drawings that are duplicated and returns a 0, else 1 ex.-iif(dcount("dwg_no","detailed plan","[dwg_no]='"&[dwg_no]&"")>1,0,1)- on another sheet I'm trying to create a formula table that has the dates in m-yyyy format across the top (10-2003, 11-2003, etc.) and planned starts / actual starts along the side- I want a formula that will sum all the dates that match if they are original- Say Planned start is in sheet 1 column a, and original is in sheet 1 column b, the date I need will change from cell to cell across the row. So the formula I have is for 10-2003(c1) is: =sumif(sheet1!$a:$a,c1,sheet1!$b:$[img]/forums/images/smilies/cool.gif[/img] but it keeps returning 0 when I can see that is at least 1 orginial drawing supposed to have started in Oct. Can anyone tell me what is wrong w/ this formula and why it won't sum the data- it shouldn't matter that criteria for the formula is linked, should it?

  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: formulas from linked db (9.0/2001)

    Are your dates exactly duplicated? Not in format, but actual number. If your date in C1 is (eg) 2/1/2004 (formatted: "02-2004") and a date in col A is 2/10/2004 (formatted "02-2004") even though the formatted text matches ("02-2004" = "02-2004") the value in the cells do not (2/1/2004 <> 2/10/2004). Excel compares the values.

    If this is the issue, you could create a column of Text dates (E1: =Text(A1,"mm-yyyy") which is text and then use something like:
    =sumif(sheet1!$e:$e,text(c1,"mm-yyyy"),sheet1!$b:$[img]/forums/images/smilies/cool.gif[/img]
    to do the lookup with the text (which "ignores" the date difference)

    Steve

Posting Permissions

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