+ Reply to Thread
Results 1 to 10 of 10

arrays, OLE action errors, IndexMatch and Calendars

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-16-2013
    Location
    Dallas, Texas
    MS-Off Ver
    O365 with latest Excel
    Posts
    107

    arrays, OLE action errors, IndexMatch and Calendars

    Excel Geniuses, I'm in need of some inspiration here.

    I am attaching a file that has 3 tabs currently.

    I have '2019query' which isn't live in this file. in addition to the query table, I have added several columns to help create a calendar of summer events at different campus locations.
    The locations tab has the table of locations plus a countifs for the total number of programs that a campus has. Next to that table I have a series of columns with indexmatch used to pull the specific campuses that have 1 program, 2 programs, etc.
    The tab called '1' is my actual trouble right now.

    You will see a drop down menu using validation for the campuses with only 1 program, an array pulling that data from the query, and finally a calendar.

    So I'm having trouble with the array and the formulas below the array.

    My array is supposed to pull in the matching records based on the drop down selection. Specifically, it is supposed to pull the data in columns BX:EB.
    Under the array, I have a concatenate formula in row 18. It is supposed to check to see if there is anything in the first row for that day and add it with a CHAR(10) to begin a new row for the next item - or skip it all the way through the array.
    Row 19 is supposed to sum the total number of students expected for that day based on if there is a program listed for that day.

    Finally, my calendar is just a series of basic if statements.

    In my real file, with the working query, the array worked just fine, so I'm not sure why that isn't working in the clean version I uploaded. but the sum in row 19 is not seeing an empty array cell value as really being empty. so it sums the students even when there isn't a program.

    BTW - if you have a sweeter, better, cooler way to create this whole thing from my query...I'm ALL EARS!!!! This is a series of headaches to make something that I wich could just be easy.

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: arrays, OLE action errors, IndexMatch and Calendars

    The reason you're not seeing the data you expect is because there are #N/A values in your data table. Try this array formula, entered in F5 with ctrl+shift+enter:
    =IFERROR(INDEX('2019query'!BX$2:BX$600,SMALL(IF(IFERROR('2019query'!$CA$2:$CA$600=$A$1,FALSE),ROW('2019query'!$CC$2:$CC$600)-ROW($J$2)+1,""),ROWS($1:1))),"")
    The red portion turns the data table errors into FALSE, which then allows your formulas to work. Does that return the results you're expecting?

  3. #3
    Forum Contributor
    Join Date
    05-16-2013
    Location
    Dallas, Texas
    MS-Off Ver
    O365 with latest Excel
    Posts
    107

    Re: arrays, OLE action errors, IndexMatch and Calendars

    So the array works just fine in my original file, just not the one I uploaded (no idea why that would affect anything...)

    But my main problem now is the formulas in row 19 under my array. I'm trying to have it do a sumifs for any program happening that day, but currently it just adds every program.

    I think the problem is that I'm technically finding something (not null) in each cell of my array, so it is adding the students for all of the programs even if you can't see them on that day. Basically, the array is giving the sumifs something to say, "yep, there's a program that day" even though it's just the empty cell returned by the array.

    Did that make any sense?

    Here's my formula for row 19:
    =SUMIFS($BK$5:$BK$16,M5:M16,"<>")

    I'm certain that the trick lies in the "<>" part of the formula...I just can't find a way to make excel see an empty return value in an array as really a null value. I tried 0 and "<>0" but no luck.

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: arrays, OLE action errors, IndexMatch and Calendars

    The problem appears to be column I on sheet '1'. It has a #REF in your uploaded file. If you skip that column with the array formula, and make sure this formula is in J5 and drag over and down, you should see the results you want:
    =IFERROR(INDEX('2019query'!CA$2:CA$600,SMALL(IF(IFERROR('2019query'!$CA$2:$CA$600=$A$1,FALSE),ROW('2019query'!$CC$2:$CC$600)-ROW($J$2)+1,""),ROWS($1:1))),"")
    Does that solve the issue you're seeing?

  5. #5
    Forum Contributor
    Join Date
    05-16-2013
    Location
    Dallas, Texas
    MS-Off Ver
    O365 with latest Excel
    Posts
    107

    Re: arrays, OLE action errors, IndexMatch and Calendars

    Melvosh - thanks, but that's the part that is working in my actual file. It is only not working the in the uploaded one.

    I'm in a quandary with the sumifs in row 19 now.

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: arrays, OLE action errors, IndexMatch and Calendars

    Just to make sure I understand, if you select '004-JYJY' in cell $A$1 on sheet '1', are you expecting 0 in column M, and 30 in column R on row 19?

  7. #7
    Forum Contributor
    Join Date
    05-16-2013
    Location
    Dallas, Texas
    MS-Off Ver
    O365 with latest Excel
    Posts
    107

    Re: arrays, OLE action errors, IndexMatch and Calendars

    yes - that is exactly what I want to happen

  8. #8
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: arrays, OLE action errors, IndexMatch and Calendars

    Try this in M19 and drag over:
    =SUMPRODUCT(--(M5:M16<>""),$BK$5:$BK$16)
    SUMPRODUCT adds overhead, but it will do what you want. There may be a tweak to SUMIF to do what you're looking for, but I'm not sure what it might be. SUMIF is counting the cells with formulas, because technically they are not blank or empty.

  9. #9
    Forum Contributor
    Join Date
    05-16-2013
    Location
    Dallas, Texas
    MS-Off Ver
    O365 with latest Excel
    Posts
    107

    Re: arrays, OLE action errors, IndexMatch and Calendars

    YES!!!! That works!!!

    I am very grateful!!!!

  10. #10
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: arrays, OLE action errors, IndexMatch and Calendars

    Happy to help!

+ 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. Send reminder emails for action items to action owners via Excel VBA to outlook
    By Ravisca2003 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2019, 10:26 AM
  2. [SOLVED] Performing action based on evaluation of two arrays
    By Sherpa21987 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-06-2017, 11:09 PM
  3. Replies: 3
    Last Post: 02-28-2016, 10:51 AM
  4. My Action Tracker Keeps placing my competed action rows on sheet2 in the same place.
    By billybong33 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2014, 04:01 PM
  5. Determining Arrays and Errors
    By CrazyFileMaker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2010, 04:27 AM
  6. If column A completely empty, one action; if not, another action?
    By StargateFanFromWork in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-15-2006, 04:15 PM
  7. [SOLVED] Need Help with an IndexMatch
    By JPW in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2005, 12:05 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