+ Reply to Thread
Results 1 to 5 of 5

some formulas using dates are not working ranges named using counta

  1. #1
    Forum Contributor
    Join Date
    12-23-2007
    Location
    chennai india
    MS-Off Ver
    2010
    Posts
    111

    some formulas using dates are not working ranges named using counta

    Attached sample file. cells marked with red are not working. cells are named using counta of d column. because in d column there is no blank cell. feel there is error while using dates. Thanks in advance as this forum has solved many queries.duplicate of brs 2015-16.xlsx

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    2,523

    Re: some formulas using dates are not working ranges named using counta

    I believe the problem is because the named ranges you are using reference row 1.
    Change the first part of your named ranges to row 2.

    Here is your formula for bookdate:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Change Sheet1!$B$1 to Sheet1!$B$2. Do that with your other named ranges also.
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,807

    Re: some formulas using dates are not working ranges named using counta

    And change the formula for your Dynamic Named Ranges to use INDEX rather than INDIRECT. Same effect but INDIRECT is volatile and INDEX isn't.


    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    03-31-2014
    Location
    dubai
    MS-Off Ver
    Excel 2013
    Posts
    51

    Re: some formulas using dates are not working ranges named using counta

    Try the following formulas

    J7 = "=SUMIFS(E:E,B:B,">="&DATE(2015,4,1),B:B,"<="&DATE(2015,4,30))"
    K10 = "=SUMIF(A:A,"<="&J10,deposits)-SUMIF(A:A,"<="&J10,issues)"
    L10 = "=SUMIFS(F:F,B:B,">="&DATE(2015,4,1),B:B,"<="&DATE(2015,4,30),D:D,D4)"

    If you wanna use your current formulas try renaming the range names as follows

    bankdate =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$D:$D))
    for bookdate change $A$2 to $B$2, for citric $H$2 and so on

    for range name full use =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$D:$D),8)
    Last edited by smb_146; 04-14-2015 at 12:57 PM.

  5. #5
    Forum Contributor
    Join Date
    12-23-2007
    Location
    chennai india
    MS-Off Ver
    2010
    Posts
    111

    Re: some formulas using dates are not working ranges named using counta

    As i was preoccupied I could not reply. Very much thanks to all of you. Presently I have changed to row2 and date by marking "" in between.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] OFFSET COUNTA Named ranges in a table
    By dma1976 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2014, 10:36 AM
  2. Working with Named Ranges
    By ptmuldoon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-20-2014, 01:39 PM
  3. [SOLVED] Using Named Ranges in Formulas
    By neshev in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-03-2012, 01:20 PM
  4. Named Ranges and Formulas
    By vaneagle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2008, 11:17 PM
  5. [SOLVED] Problem Working with Named Ranges
    By montgomerymouse in forum Excel General
    Replies: 2
    Last Post: 01-10-2005, 07:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1