Posts by wizcreations

• Complex INDEX and SMALL or MATCH based on muliple criteria

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:

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

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

Have fun!

• Complex INDEX and SMALL or MATCH based on muliple criteria

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!

• Macro sort by ColA from A3 to BQ .Rows.Count

Re: Macro sort by ColA from A3 to BQ .Rows.Count

Thank you for the help!

• Macro sort by ColA from A3 to BQ .Rows.Count

Re: Macro sort by ColA from A3 to BQ .Rows.Count

I did have one empty place as a separator. I just put a single space into that location.

• Macro sort by ColA from A3 to BQ .Rows.Count

Re: Macro sort by ColA from A3 to BQ .Rows.Count

What if a few columns do not have information entered? How can I ensure it will sort everything from the first to last column?

• Macro sort by ColA from A3 to BQ .Rows.Count

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.

• Create/Update sheets based on added/modified information on master sheet

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

Oh yeah... I just realized that MoveSheets was a separate function so the previous value wouldn't be copied over from the CreateCadetSheets function.

Thanks again!

• Create/Update sheets based on added/modified information on master sheet

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

Here is the final version. Thank you for sticking to it and helping me SO much these past few days!

• Create/Update sheets based on added/modified information on master sheet

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:

• Create/Update sheets based on added/modified information on master sheet

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"

• Create/Update sheets based on added/modified information on master sheet

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

I think I may have fixed it...?

• Create/Update sheets based on added/modified information on master sheet

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.)

• Create/Update sheets based on added/modified information on master sheet

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.

• Create/Update sheets based on added/modified information on master sheet

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.

• Create/Update sheets based on added/modified information on master sheet

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?

• Create/Update sheets based on added/modified information on master sheet

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

Here you go. I deleted all the individual sheets and clicked update. It created the first one and then had a problem.

• Create/Update sheets based on added/modified information on master sheet

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

I get Runtime Error 9 on the next 2 lines. Subscript out of range.

Thank you for your continued help with this. I really appreciate all the time you're putting into this for me.

• Create/Update sheets based on added/modified information on master sheet

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

It seems that the script does not add sheets if there are no 'x' marks in any of the options. Is that what this part of the code is doing?

Code
``````x = Application.CountIf(.Rows(i), "x")
If x > 0 Then``````
• Create/Update sheets based on added/modified information on master sheet

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

Code
``````If ws Is Nothing Then
Set ws = Sheets.Add(after:=Sheets(1)).Name = sn
End If``````

this causes 'Runtime Error 424.' ???

• Create/Update sheets based on added/modified information on master sheet

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

Quote from wizcreations;572730

The lists with the word "Additional" in the title will be updated manually for each person.

If I enter info under the titles Additional Awards or Additional Extra Curriculars, I don't want that information to be erased each time the document is updated.