Search results

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.

In the attached, such a regex version, converted to a named lambda FilterTitles; when used has hints for what goes where in the function's arguments. See cells F5 and H5. FYI only, the longhand lambda is in cell M5 See the named lambda function in Name Manager To use it in your own workbook...

try:=FILTER(tb_titles[TITLES],BYROW(tb_titles[TITLES],LAMBDA(a,SUM(IFERROR(SEARCH(tb_exclusions[EXCLUDE],a),0))=0))) I think I could do better, maybe with the new REGEX functions.

Take the ,TRUE out that I highlighted with a red oblong in my msg#11, and if you want to see repeating months then take out the UNIQUE wrapper too.

There is no Delhi in Sept for A123