User Profile
mtarler
Silver Contributor
Joined Feb 28, 2020
User Widgets
Recent Discussions
Re: Excel Sum Product / Date Range
as noted by Sergei it is not only possible but preferred. Instead of DATEVALUE(xxx) or DATE(xxx) just put the cell reference where that date can be found. Or you can even do a lookup in a table for which date to use. so the above formula could use Z1 and Z2 as the dates like so: =COUNTIFS(A2:A317,"<=" & $Z$1, B2:B317,">=" & $Z$1, B2:B317,"<=" & $Z$2) If you need additional help I suggest maybe opening a new thread and give more specifics of what you are trying to do or what isn't working.Re: How to wrap table, not vector (WRAPCOLS for tables)
Zdenek_Moravec alternatively this formula doesn't use any Lambda but in no way am I saying it is better or more efficient, it was just a self-challenge for a different approach: =LET(in, A1#, wrapAt, 21, TEXTSPLIT(TEXTJOIN(",",FALSE,HSTACK(WRAPCOLS(TEXTSPLIT(TEXTJOIN(",",FALSE,EXPAND(in,,COLUMNS(in)+1,";")),,",;,"),wrapAt,""),EXPAND(";",wrapAt,,";"))),",",";",TRUE,,""))Re: formula for scheduling help
The basic answer is yes you can. I wish you would provide a sample sheet (no private info please and if those are real names above that should be redacted for their privacy). That said there are a number of improvements to the master table and other tables that could make the formula and process easier including making headers and label consistent and making the master table 'Formatted as a Table'. I will also make a few assumption including, the master table is on a sheet called 'Master', the header cell 'First Name' is cell B3, there is a 'Last Name' column that is hidden (thank you for doing that) and in C3, and the assignment range is I4:Z100, the upper left corner of the first table with Tamura's name I will assume is cell B4 and that the header has cells that are merged sometimes for the date and the 'hidden' cell is empty. the formula would be something like: =XLOOKUP( IF(LEFT($A4,5)="CHECK", "Yellow", IF($A4="", TEXTBEFORE($A3," CLASS")&" 2", TEXTBEFORE($A4," CLASS")&" 1")), INDEX(Master!$I$4:$Z$100,0,XMATCH(IF(B$2="",A$2,B$2),Master!$I$2:$Z$2)+(B$2="")), Master!$B$4:$B$100&" "&Master!$C$4:$C$100, "") As you can see it is a basic XLOOKUP with a lot of conditions inside to deal with your headers and offsets and such. The first line is creating what to look for. second line is pulling the correct column in the master table the 3rd line is creating a column of first name - last name to output even if this doesn't quite work I hope it puts you on the right track for that top table and hopefully the next table too. If you don't know already, the '$' character is put before a reference range you don't want to change when you copy or fill. So I 'locked' row 2 in some cases or column A or in the case of the table ranges both the columns and rows.Re: Excel formula to count how many times the same 5 names show up with the same date. aka NBA lineup
Hendew44 This should work (but there may be easier ways): =LET(roster_list, B2:D31, name_list, G2:G6, sorted_starters, BYCOL(WRAPCOLS(TAKE(roster_list,,-1),5),LAMBDA(a, TEXTJOIN(",",,SORT(a)))), sorted_name_list, TEXTJOIN(",",,SORT(name_list)), SUM(--(sorted_name_list=sorted_starters))) EDIT: for example this is slightly more concise: =LET(roster_list, B2:D31, name_list, G2:G6, starters, WRAPROWS(TAKE(roster_list,,-1),5), SUM(--(MMULT(--ISNUMBER(XMATCH(starters,name_list)),{1;1;1;1;1})=5)))Re: Can you use AND / OR in an INDEX MATCH
so I see you are actually using google sheets and not excel so i don't even have to ask what version, which is good because you should use some newer functions. if I'm reading your formula right try: =LET(first, XLOOKUP(B20, 'NM & CRM'!$D$4:$D, 'NM & CRM'!$C$4:$C,""), IF(first<>"",first, XLOOKUP(B20, 'NM & CRM'!$I$4:$I, 'NM & CRM'!$H$4:$H,"")) basically do the first lookup and assign it to 'first' then check if a value was found and if not do the second lookup.Re: Conditional Formatting Help
CatherineMadden Highlight the column of names and then select conditional formatting -> New Rule Then select to use a custom formula and type in =$B1>$C$1 and set the formatting accordingly: The key here is that the number 1 in B1 does NOT have a $ before it (the B can so $B1 is good) and the 1 in C1 MUST have the $ before it (the C doesn't have to but can so $C$1 works)Re: How to Extract a 2-Column List from a Table
B_Coy assuming you have 365 then try this: =LET(in,A2:E21, cols,TOROW(HSTACK(SEQUENCE(COLUMNS(in)-2,1,1,0),SEQUENCE(COLUMNS(in)-2,1,3))), twoCols, WRAPROWS(TOROW(CHOOSECOLS(in,cols)),2), UNIQUE(FILTER(twoCols,TAKE(twoCols,,-1)<>0,""))) in line 1 you change the range to the whole table (or just use the table name if it is formatted as a table. line 2 creates a list like 1,3,1,4,1,5,1,6... for the size of the table line 3 pulls the columns in the order defined in line 2 and then reformats it back to 2 columns line 4 filters all the lines with 0 / blank values and for unique valuesRe: Excel for Web: Control equivalent to the VBA messagebox
I wonder ... could you had the script insert and show that new sheet with the message and also add an 'OK' button that is linked to another script that will then hide that sheet? I've never tried to have a script add a button to another script and don't recall a way to do that off hand, but that could be slick. If not, can you pre-create a sheet in the workbook with the colored range and pseudo message box styling and the OK button to hide that sheet so then the script only needs to unhide that sheet and insert the corresponding message in the correct range.Re: Excel Formulas (Indirect(index(match)))
a couple questions: are you using excel365 are all the tables on the TestFund(n) worksheets laid out the same on the same ranges then you can use VSTACK(TestFund(1):TestFund(100)!A1:Z100) to stack all the data from all the sheets to do the searches on. You can also add each of the sheets to the data model and then use a pivot table or use power query That all said it is hard to give more specific answer without a sample sheetRe: Excel Formulas (Indirect(index(match)))
I'm not sure if this is just a learning thing or maybe just an example as what I see could easily done with 1 formula. The whole range on aggregate A16:E20 could be this formula in A16: =TRANSPOSE(TestFund!A6:E10) I really don't see the need for SUMIF but lets assume a very generic case and using just index and match: =index($B$7:$E$10, match($A17,$A$7:$A$10,0), match(B$16, $B$6:$E$6, 0) )
