+ Reply to Thread
Results 1 to 17 of 17

COUNTIFS formula that changes Range by itself every new day.

  1. #1
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Question COUNTIFS formula that changes Range by itself every new day.

    Hi all,

    tried to make a simple file for my job to count stuff automatically.
    this does seem to work just fine with a COUNTIF formula. the problem here is that the range from the formula changes every new day by itself..
    the formula has to look for its data to another worksheet inside the workbook which is linked there with an external workbook.
    it's set up in a way that i only have to "re-save" all required files on their destination and then refresh the homefile(where the problem occurs).
    the refresh updates the links with the external files and gives me a count on how many trailers that have been ordered. example of the countif formula below

    ex: =COUNTIFS('order trailer Today'!H21:H100, "W*")

    above formula does work! but when i refresh all my files (next day) the range in the formula changes by itself..

    ex: =COUNTIFS('order trailer Today'!I21:I100, "W*")

    does anybody know a solution for this? thank you in advance!
    kind regards

    Miguel

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: COUNTIFS formula that changes Range by itself every new day.

    Have you tried using absolute>?
    =COUNTIFS('order trailer Today'!$H$21:$H$100, "W*")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: COUNTIFS formula that changes Range by itself every new day.

    Hi,

    Is the order/number of columns in the source sheet changed by the data refresh? (that can happen if it's a data query)
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    Hi, thank you for mentioning but yes, i did try this..
    no succes..

    any other options?

  5. #5
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    no, the column is in the exact same spot. only my range changes..

  6. #6
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    i did use a data query though.. but the ranges are still the same.

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: COUNTIFS formula that changes Range by itself every new day.

    I suspect your data query is moving the cells in some way- it's the only reason that the formula would alter. I suspect it would be more robust if you could use table references or a combination of index and match to locate the relevant column header, but we would need to see a sample workbook in order to figure out the best combination.

  8. #8
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    ok, will try to upload the file asap.
    the strange thing is that i have the same formula 5 times in this file and only 2 of them change the range. all the others remain the same..
    they each do refer to a different Data query.

    file is coming

  9. #9
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    how can i send the attach? it doesn't seem to work? *sigh i'm so pro..* :p

  10. #10
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    aha found it!

    attach here,

    the error is next to the RED cell.(at the bottom somewhere)
    Attached Files Attached Files
    Last edited by Mr Munchiez; 09-13-2017 at 04:12 AM.

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: COUNTIFS formula that changes Range by itself every new day.

    If the table layout should not change, I suggest that you right click the table on the 'order trailer Today' sheet, choose Table- External Data properties, and select the 'Overwrite new existing cells with new data' option.

  12. #12
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    Ok, i've tried this!
    adjusted all tables to the option 'Overwrite new existing cells...' hope this will work! i will keep you updated. Normally tomorrow, first thing of the day (6o'clock in the morning).

    thank you so far for all the help, i will update tomorrow! ;-)

    greetz Miguel

  13. #13
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    updated all files and tried to open the file. no succes..
    it's the only formula that changes, all others that are in it keep the same range. even if they have a connection with the same tab as the changing formula.
    only that strange one changes its range to something else.
    It changes to range: I42-I96... it's so weird.

    any other ideas?

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: COUNTIFS formula that changes Range by itself every new day.

    A brute force workaround would be to use INDIRECT
    =COUNTIFS(INDIRECT("'order trailer Today'!H21:H100"), "W*")

  15. #15
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    i'll try this! why is it a brute force workaround?

    the stupid thing about this is that it only changes the next day. so i'll only know tomorrow if this works..
    thanks anyway

  16. #16
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: COUNTIFS formula that changes Range by itself every new day.

    Most workarounds are brute force. I don't like INDIRECT as it is volatile but you only have one and it's a simple formula so it should not be an issue.

  17. #17
    Registered User
    Join Date
    09-12-2017
    Location
    Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: COUNTIFS formula that changes Range by itself every new day.

    XLNITWIT, this works!! you're amazing!
    thank you so much for thinking with me, it has helped me a lot.

    i'lll put [SOLVED] with this thread.

    thanks again and have a nice day ;-)

    Miguel

+ 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. countifs with critera range made from a formula
    By displaced in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2017, 10:17 AM
  2. [SOLVED] Named Dynamic Range and Countifs formula
    By AliJWood in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-11-2017, 06:15 AM
  3. Time range criteria using countifs formula
    By KMOCEK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2013, 05:30 PM
  4. Replies: 1
    Last Post: 08-09-2013, 10:30 AM
  5. Macro to add range & criteria in COUNTIFS Formula
    By wspettus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-28-2011, 03:34 AM
  6. Using Date Range in Countifs formula
    By Kenny_D in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-09-2010, 10:57 AM
  7. COUNTIFS formula as a range
    By yadaaa in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-04-2008, 01:06 PM

Tags for this Thread

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