Search results

and it goes on: =SORT(UNIQUE(TOCOL(CHOOSECOLS(A2:D15,1,3,4),1))) Table version:=SORT(UNIQUE(TOCOL(CHOOSECOLS(Table1,1,3,4),1)))

The article is here: https://chandoo.org/wp/9-box-talent-map-template/ and there's a link to the workbook therein here: https://chandoo.org/wp/wp-content/uploads/2020/11/9-box-grid.xlsx I strongly suspect @Trashman is using O365 since he refers to it.

A start: At cell D2 of the Action sheet in the attached workbook is your table of Sales Order numbers that you want included in the other 3 sheets. Because this is a proper Excel table, when you edit this table make sure that all the order numbers you want included are within the confines of...

Cell E2:=IF(MAXIFS(C2:C30,A2:A30,A2:A30)=C2:C30,"Latest","")

In a cell in row 2: =MAXIFS($C$2:$C$23,$B$2:$B$23,B2)=C2 and copied down will show true/false (true if the latest date). However, this assumes that your 'given customer' is based on column B (AccountName). If this is not the case, the and the given customer is based on column A then it becomes...

Taking @Debaser 's idea a bit further by not having a new column step, with the likes of:= Table.SelectRows(Source, each not (List.AllTrue({[Invoice Balance2]=0,[Debit Memo Balance]=0,[Credit Balance]=0,[#"Unapplied Payment "]=0,[#"On-a/c Credit Memo "]=0,[#"Account Bal3"]=0})))where Source is...

Updated attachment in my last message.

Correction: You would need a refresh if you change the Hols table too, however, in the attached I've moved all the tables to a Tables sheet. When you move away from that sheet the query is refreshed (a one line macro).

Oops, there is now.

Yes, you'd need to refresh the table at cell K2 (right-click and choose Refresh) if you change the Courses table at cell C2. We could automate this if you want. At the moment, a change in size of that table will probably shift cells around elsewhere on that sheet so perhaps put that table in a...

I have updated the file attached to msg#5

See attached.

Have a look at the light green shaded area (d15:h17) in the attached. If this is what you want then we can tidy it up (it's very rough and ready at the moment). It uses a combination of Power Query, named ranges and a one-line macro to do the job. Don't alter the formula in cell N2 (yet). You...

See the two pivot tables in column N in tha attached.

You have monthly subtotals in the plot, they shouldn't be there.

re: =SUM(L153-K153)*24 The SUM part is superflous, all you need is =(L153-K153)*24 which is what I proposed in msg #3. It's to do with something called 'operator precedence' (internet search it); the order in which calculations are done. Multiplications are done first then subtractions...

See in the attached whether the last step in query Table3 (2) called AddedCustom is giving you the expected results. it uses List.Sum() which ignores null values: =List.Sum({[#"[A]Current (Home Currency)"],-[#"[B]Unapplied Payment (Home Currency)"],-[#"[C]On-Account Credit Memo (Home...

No, but it does equal right days (and fractions of days). All dates and times in Excel are in units of days. 12 hours (half a day) is 0.5 (put 0.5 in a cell and format at it as [HH]:MM and you'll see 12:00. The underlying value will still be 0.5). CONVERT(F29-C29,"day","hr") only multiplies...

…now where in Hull?

=(F25-C25)*24 ? Ensure Excel doesn't automatically convert the cell format to time or date after you commit the formula to the sheet (make sure it stays as a plain number or General).

Does it have to be vba? In cell E1: =SUM(--ISNUMBER(SEARCH("HRG",B3:B12))) This is a case-insensitive search, for a case-sensitive version change SEARCH to FIND Depending on your version of Excel, you may have to commit this formula to the cell with Ctrl+Shift+Enter rather than the usual Enter...

One way: Get your value in the named single cell dataset_actual as follows: I chose to name the step ColmHdr: ColmHdr = Table.FirstValue(Excel.CurrentWorkbook(){[Name="dataset_actual"]}[Content]) Then in the add column step use the likes of this: = Table.AddColumn(Source, "Added Colm", each...

Now that I understand what you want (thanks @AliGW !) there's another formula that you could use which also means you don't need the TEXTSPLIT in column BI of the first sheet. In D10:=BYROW($B10:$B66,LAMBDA(a,IF(ISNUMBER(SEARCH(a,XLOOKUP(D2,'RepHunter Contacts - Member...

In the attached is a very clunky offering. On sheet page 1 there's a button at cell M3 which calls a macro Test, which in turn calls the macro blah with 3 arguments being in order: the cell with the lengthy string the cell where the output starts a range of cell(s) having the same width that...

Why?! =INDEX(TOROW(FILTER(IF($C$13:$K$23=C5,$C$12:$K$12,NA()),$B$13:$B$23=C3),3),1) will give you the first result. =INDEX(TOROW(FILTER(IF($C$13:$K$23=C5,$C$12:$K$12,NA()),$B$13:$B$23=C3),3),2) will give you the second result etc.