+ Reply to Thread
Results 1 to 6 of 6

Excel array formula and structured table date header fails with date number <10

  1. #1
    Registered User
    Join Date
    09-03-2014
    Location
    Perth
    MS-Off Ver
    2010
    Posts
    3

    Excel array formula and structured table date header fails with date number <10

    I have a formula that matches the date with a structured table header row. I have extensive code that deals with the text string nature of this row successfully.

    However, when trying to check visible values in the column according to date, the array formula I am using works fine for double digit dates like "11-Sep-15" but not effectively single digit like "01-Sep-15". I have even tried not having a leading zero (as it's text) and it still works fine with double digit and not single...

    A sample of the formula is as follows;

    {=SUM(--( INDEX(SiteAdmin, 0, MATCH(N23&"",SiteAdmin[#Headers], 0))=$D$7)*(--(SUBTOTAL(3,OFFSET( INDEX(SiteAdmin, 0, MATCH(N23&"",SiteAdmin[#Headers], 0)),ROW( INDEX(SiteAdmin, 0, MATCH(N23&"",SiteAdmin[#Headers], 0)))-MIN(ROW( INDEX(SiteAdmin, 0, MATCH(N23&"",SiteAdmin[#Headers], 0)))),,1)))))}

    D7 is a check cell with a value of "On" N23 is a check cell where I can play around with text values to test the formula.

    I have checked that the value of the header cell is the same as what is being passed to the formula using =SiteAdmin[[#Headers],[01-Sep-15]]=Calcs!N23 and that the value is indeed text by using =ISTEXT(N23)...

    Help!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Excel array formula and structured table date header fails with date number <10

    Please post a file showing expected results.

  3. #3
    Registered User
    Join Date
    09-03-2014
    Location
    Perth
    MS-Off Ver
    2010
    Posts
    3

    Re: Excel array formula and structured table date header fails with date number <10

    The results of the formulas I have put into another table. The current results are as follows;

    Excel Picture.jpg

    The expected results would be the numbers calculated as per the dates above 10...

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excel array formula and structured table date header fails with date number <10

    sdassira welcome to the forum

    The request was for a sample file, we cannot really work with pics
    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

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Excel array formula and structured table date header fails with date number <10

    Also, please re-read the forum rules, in particular that one relating to "cross-posting":

    http://superuser.com/questions/96342...uble-digits-an

    Not very nice to work on a problem only to find that it's been solved elsewhere, as I'm sure you can imagine. Of course, you probably think that you're simply "hedging your bets" that way, though try looking at it from the other side and I hope you'll see what we mean.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Registered User
    Join Date
    09-03-2014
    Location
    Perth
    MS-Off Ver
    2010
    Posts
    3

    Re: Excel array formula and structured table date header fails with date number <10

    Thank you for taking the time to post. Though I have made use of these forums over the years this is the first problem that I've posted. My apologies for not paying attention to the rules. I will close this thread.

+ 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] Splitting date fails for a part of date column due to different cell format
    By Moriexcel in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 11-21-2014, 01:00 PM
  2. [SOLVED] Update Table Header Date with VBA
    By IronCladRooster in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-11-2014, 09:24 AM
  3. Replies: 2
    Last Post: 05-28-2014, 06:52 AM
  4. Semi-structured bloomberg ticker data to go into a clean 3 header table
    By jasonleewkd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-23-2013, 12:12 PM
  5. [SOLVED] Excel table - using a formula in a structured reference
    By jankee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-22-2013, 08:10 AM
  6. Replies: 2
    Last Post: 04-08-2012, 03:36 AM
  7. Counting Number Of passes & fails by date + product
    By ajxxx in forum Excel General
    Replies: 2
    Last Post: 01-06-2009, 07:30 AM

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