Some cells work with formula, some do not, and I can't figure out why - Page 2

Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Some cells work with formula, some do not, and I can't figure out why

  1. #11
    Board Regular
    Join Date
    Aug 2012
    Posts
    243
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Some cells work with formula, some do not, and I can't figure out why

     
    Hi,

    So Excel is definitely seeing them as dates then.

    Just looked at your screenshot again, the second date in the =COUNTIFS that is visible in the formula bar is not in the format "d/m/yyyy" as it is in your post. it is in the format "d, m, yyyy". This won't work the same.

    I would suggest changing the formula as follows:

    =COUNTIFS(Wells!$AC$3:$AC$2002,">=1/"&A3&"/"&LEFT($A$1,4),Wells!$AC$3:$AC$2002,"<=" & EOMONTH("1/"&A3&"/"&LEFT($A$1,4),0))

    You can then just drag the formula down.

    Adjust as follows:
    Change both instances of A1, to whichever cell contains the text "2017 DATA"
    Change both instances of A3, to whichever cell "JANUARY" is in.

    Ensure dollars remain in A1, and no dollars in A3.

    Use the same formula for assessing column AA amended accordingly.

    Not sure if this may solve the issue, but worth doing either way.

    Cheers
    JB

  2. #12
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Some cells work with formula, some do not, and I can't figure out why

    Quote Originally Posted by bellman101 View Post
    Hi,

    So Excel is definitely seeing them as dates then.

    Just looked at your screenshot again, the second date in the =COUNTIFS that is visible in the formula bar is not in the format "d/m/yyyy" as it is in your post. it is in the format "d, m, yyyy". This won't work the same.

    I would suggest changing the formula as follows:

    =COUNTIFS(Wells!$AC$3:$AC$2002,">=1/"&A3&"/"&LEFT($A$1,4),Wells!$AC$3:$AC$2002,"<=" & EOMONTH("1/"&A3&"/"&LEFT($A$1,4),0))

    You can then just drag the formula down.

    Adjust as follows:
    Change both instances of A1, to whichever cell contains the text "2017 DATA"
    Change both instances of A3, to whichever cell "JANUARY" is in.

    Ensure dollars remain in A1, and no dollars in A3.

    Use the same formula for assessing column AA amended accordingly.

    Not sure if this may solve the issue, but worth doing either way.

    Cheers
    JB

    I missed a cell when rewriting it for the eighth time. I think I have an eye twitch. This database is driving me nuts.

    So in essence, instead of including the dates, reference back to a cell and have Excel calculate end of month? I assume I can remove the left fn and just put in 2017 etc for the table titles?

    (Add-in note: this database will be used by everyone in the office, including those who had no idea Excel was anything more than a fancy table-maker and actually calculates stuff, so the less stuff they can break by entering in data on the appropriate sheets, the better, hence the drop down menus. I'm hoping I can lock specific sheets so they can view the tables/charts, but not change anything on those sheets.)

  3. #13
    Board Regular
    Join Date
    Aug 2012
    Posts
    243
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Some cells work with formula, some do not, and I can't figure out why

    That would be my method I think, and yes, '2017' will do exactly the same there. The LEFT() would just reduce the number of changes required at New Year, but a CTRL+H in formulas should work just as well!

    One final note, it looks like you fields are only date fields, but if you ever use this with date & time fields, be sure to say less than 1st of next month. Several ways of getting there. easiest just to add '+1' after the EOMONTH function.

    "<=31/JAN/2017" will not include "31/JAN/2017 09:30:00", as the actual value of just the date is 42766, whereas the datetime is 42766.4-ish, so greater than the date on its own.

  4. #14
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Some cells work with formula, some do not, and I can't figure out why

    I actually have tables set up for the next five years. Hopefully at that point I've convinced them to actually get tracking software, since we're also adding ordinances left and right that will require more permits & tracking (and more staff, but that's not that likely to happen). It's been a few years since I had to do anything exciting with Excel and it turns out I've forgotten a decent sized chunk. I remember just enough to be dangerous in all the wrong ways.

    Good to know on the times!

    Blocked off this morning to fix the cells with the method you've put above.

  5. #15
    Board Regular
    Join Date
    Aug 2012
    Posts
    243
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Some cells work with formula, some do not, and I can't figure out why

    Haha, well good luck.

    Give me a shout if you get stuck

  6. #16
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Some cells work with formula, some do not, and I can't figure out why

    Quote Originally Posted by bellman101 View Post
    Haha, well good luck.

    Give me a shout if you get stuck

    Woohoo! It worked! Thanks!

  7. #17
    Board Regular
    Join Date
    Aug 2012
    Posts
    243
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Some cells work with formula, some do not, and I can't figure out why

      
    Great to hear!

    You're very welcome

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com