Posts by wizcreations

    Here's the solution! As I suspected, logical operators are disfunctional inside an array formula; they can only return a single result of TRUE or FALSE, not an array of TRUE and FALSE. Here's the workaround that worked for me:


    Instead of IF(OR((statement1),(statement2),(statement3)),...
    I used IF((statement1)+(statement2)+(statement3)>0,...


    Similar logic works for AND
    normal usage: AND((statement1),(statement2)
    array usage: (statement1)*(statement2)


    Have fun!

    TLDR: I am fairly certain the OR function inside the array function is my problem. Is there a way to get this to work so that something like OR({FALSE,FALSE,FALSE},{TRUE,FALSE,FALSE}{FALSE,TRUE,FALSE}) will return {TRUE,TRUE,FALSE}?




    I have an excel file containing ~900 rows of information on one worksheet. Each row is designed to be represented and filtered by three categories and four options. Values for the Category are (blank), A, AB, or ABC. Values for the Option are (blank), (any combination of +, V, E, or G), or --.


    I have dropdown menus on a separate worksheet intended to allow a user to filter the complete list based on the user's desired categories and options. For example, selecting B for Category 2 will copy from the full list all rows that contain AB or ABC as the entry for Category 2. Each of the four options can be enabled or disabled. If enabled, all rows containing a +, V, E, or G for that option should be included on the list regardless of whether or not they have the B identified for Category 2. Further, any enabled Option that has a -- in the row means that row should not be included, even if the Category or another Option would otherwise add the row.


    I have an array formula written on the separate worksheet that pulls rows from the full list to the second sheet based on the selection of a single Category (#2 currently). When I attempt to modify my IF statement to include the other two categories, the function ceases to work correctly and begins to pull every row.


    I have attached a copy of my file with dummy content. Here is the function I have that works (taken from cell B10, the first and leftmost cell beneath the headers).


    {=IFERROR(INDEX('Full List'!B$10:B$26,SMALL(IF(ISNUMBER(SEARCH($D$4,'Full List'!$G$10:$G$26)),ROW('Full List'!B$10:B$26)-ROW('Full List'!B$10)+1),ROWS('Full List'!B$10:B10))),"")}


    If I attempt to add anything to the IF statement, the equation ceases to work properly. Here is the change I attempted to make. Ideally this should enable me to filter rows by all three categories.


    {=IFERROR(INDEX('Full List'!B$10:B$26,SMALL(IF(OR(ISNUMBER(SEARCH($D$3,'Full List'!$F$10:$F$26)),ISNUMBER(SEARCH($D$4,'Full List'!$G$10:$G$26)),ISNUMBER(SEARCH($D$5,'Full List'!$H$10:$H$26))),ROW('Full List'!B$10:B$26)-ROW('Full List'!B$10)+1),ROWS('Full List'!B$10:B10))),"")}


    If I could get this IF statement to work, I figure that would pave the way for me to get the rest of my functionality to also work. I imagine this would ultimately this would be a lot of OR/IF statements to check the criteria of the options and an AND/IF statement to check for the --.



    I would not consider myself an expert at Excel functions, so I'm not sure if one of the other functions the IF statement is nested into is causing the issue. Could you please help me to get this equation working with more than one criteria in my filter? Thank you very much!

    I have Column headers in rows 1 and 2. I'd like have a macro sort my data (Cols A:BQ) by the value in column A. I do not need a case-sensitive sort. The number of rows will vary so I need the macro to be able to detect how many rows there are and then sort the range A3 to BQ# where # is the number of rows.


    I tried to record the macro and then modify it but I keep getting errors. I currently only have 5 rows so the recorded macro defaults to BQ5, but if I add additional rows, it does not include the new rows.


    Thank you for your help.

    Re: Create/Update sheets based on added/modified information on master sheet


    This did it!!


    I did make these changes to get it just right:

    Re: Create/Update sheets based on added/modified information on master sheet



    Should be:


    "Cadets" - "Paul, GHI (d)" - "Bill, DEF" - "Joe, JKL" - "Mark, MNO" - "Tom, ABC"

    Re: Create/Update sheets based on added/modified information on master sheet


    I think I may have fixed it...?

    Re: Create/Update sheets based on added/modified information on master sheet


    I changed MoveSheets so that the sheets corresponding to deleted names have something that signifies them as removed from the list. Now not only are they at the front, they also have a "(d)" signifying deleted. I just commented it out because I now see that if the workbook is updated repeatedly, a new "(d)" will be added each time.


    It appears that the sheets are sorted correctly with the exception of the last name on the list. The last name defaults to the first sheet after Cadets. The code also seems to only be able to detect and move one sheet at a time so if multiple people are deleted only one name is moved to the front. (I commented out my SortSheets function so that isn't moving stuff around prematurely.)

    Re: Create/Update sheets based on added/modified information on master sheet


    Okay, I think we've just about got it! I added a rename function to add (d) to the name of sheets for deleted people.


    The code does something strange to the sorting order if the Cadets sheet is the only sheet when you click Update. All sheets are created, but I have no idea how it determines the order. However, clicking update again fixes the order.


    The last thing I noticed is that if an 'x' is deleted, the corresponding Organization or Award isn't removed from a person's page. Is there an easy fix for that or would it be too much of a hassle?



    Here is my current version. You can run it to see what exactly is happening. Try deleting someone's name from the Cadet sheet as well as removing some of the 'x' marks.

    Re: Create/Update sheets based on added/modified information on master sheet


    3. What I meant was, one of the versions you posted moved deleted names to the front of the list, but after the cadet name. You uploaded the file DatabaseV3-Reviced.
    i.e. if these are the sheets, it was [Cadets] , [Deleted Person 1] , [Deleted Person 2] , [First Name Alphabetically] ... [Last Name Alphabetically]



    I think it would be better (and easier) though to just leave them in alphabetically order and use the sheet name [Last, First (deleted)] format for names that get deleted from the [Cadets] sheet.



    Could you please make #1 and 3 happen? I'll use the sorter for #2 so it doesn't really matter where new sheets are added in the list.

    Re: Create/Update sheets based on added/modified information on master sheet


    Works!! But now for a few questions:


    1. Is it possible to make it generate the new sheets even if there are no 'x' marks for that person? Many of the names will not earn awards for quite a while.
    2. Can we have the new sheets appear at the end of the list instead of the start as they do in this latest version? If not, I can just use the Sort function I already have, but that leads to another question:
    3. I liked how you had the sheets corresponding to deleted names move to the front of the list. Can that happen again? But keep the deleted names separate from the other names? Or perhaps just rename the deleted sheets to [Last, First (deleted)] or something?