Search results

In cell D7, you can try following formula: =IFERROR(INDEX(DATA!$J$3:$AM$903,MATCH(ARREAR!$C$2&ARREAR!$B7,DATA!$C$3:$C$903&DATA!$B$3:$B$903,0),MATCH(ARREAR!D$6,DATA!$J$2:$AM$2,0)),"") which you can copy down and across. Note: You may have to use Array Entry (CTRL+SHIFT+ENTER) if you are using...

Please see if the following formula does what you need: =IF(ISNUMBER(LOOKUP(99,SEARCH(" "&$A$2:$A$25&" "," "&D2&" ",1))),"Done","Not Proper")

I have tested and therefore commented. However, we probably are referring to two different approaches. Mine is mkdir through cmd and you are approaching through VBA. Following line through cmd works fine as I have mentioned! RetVal = Shell("cmd.exe /c mkdir " & Chr(34) & FldPath & Chr(34), 1)

If root directory is available and access is available to the user running the command then MkDir doesn't require folder existence check. If the specified folder already exists then the command simply won't do anything and won't affect any existing data e.g. files or sub-directories which are...

Formula based solution in column M, N and O. In principle, logic required is around generating a repeat number 7 times and then subtracting sequentially to show the dates in the week. Note: This is fairly old approach. There may be nifty O365 functions which can do this in a shorter way.

It is possible to share if the file is on Cloud or SharePoint site even if it has Table(s). Examples would be: > Keeping it on OneDrive and sharing the file link. > Keeping it on MS Teams and sharing via Team >> Files option. You can see updates almost at real time. However, it also will...

Here's a code link which should do what you need! https://www.mrexcel.com/board/threads/trim-using-macro.638331/page-2#post-3168486 If you are looking for shorter version of code instead then: https://www.mrexcel.com/board/threads/trim-cells-help-needed.809781/post-3957874

Another option you can try is SUBTOTAL through outline. 1. Select Range for which you need SUBTOTAL (AVERAGE) 2. Choose Data >> Outline >> Subtotal 3. Choose settings as below 4. Output will appear as below. Excel will insert required rows. Bold ones in the snapshot below.

1. Create standard templates where possible with formulas so that you can enter only the changing / update part. 2. If you need to present summary then look at pivot table. 3. If you mean formulas that calculate faster than the ones you are currently using then post your data ask forum experts...

Can you give typical example(s) of "cannot find some of the items"? Maybe then people would be able to guess the probable reason.

@DashboardNovice : refer the orange cells in the uploaded file and confirm if that is what you want. Cell formatting has been set $ General

If you have access to UNIQUE and FILTER functions then you can try (might be CTRL+SHIFT+ENTER depending on your version of Excel): =UNIQUE(FILTER(A3:A19,IF(A3:A19=A4:A20,IF(B3:B19>=2,IF(B3:B19=(B4:B20-1),TRUE,FALSE),FALSE),FALSE)))

If you have access to TEXTJOIN function then you can try approach as provided in attached workbook. In cell I2, =TEXTJOIN("-",FALSE,C2:G2)&"_R"&H2 In cell J2 (which you can implement in A2)...

With O365, you can also use TEXTJOIN and TRIM, MID (usual suspects in parsing) function like below. =TRIM(MID(SUBSTITUTE(" "&TRIM(TEXTJOIN("",TRUE,IF(ISNUMBER(MID($A4,SEQUENCE(LEN($A4)),1)+0),MID($A4,SEQUENCE(LEN($A4)),1)," ")))," ",REPT(" ",99)),99*COLUMNS($A$1:A1),99))

I was about to post this link. Simple thing to note in this case is: the construct SEARCH produces a series of Numbers(greater than or equal to one) and Errors (#VALUE!)) which in turn gets converted to numbers less than 1 or #VALUE which get looked up by 2!

Wishing you a speedy recovery!

I have read in your posts about self documenting approach when it comes to writing functions and I don't really disagree with the approach in full. In fact, I had employed the simplest of table and self documenting formula approach for simple interest calculation as a test but the response left...

Hi Peter, I don't have access to some of these functions and therefore it doesn't work. However, in this particular case, the SUMPRODUCT solution is simpler and therefore easily maintainable or am I missing something here (apart from these latest functions :D )?

LOOKUP function uses binary search and it locates a value which is equal or lower than the LOOKUP criterion. Passing lower case "zz" can catch most of the possible strings. It is one of the most common tricks used inside LOOKUP construct. Another one is BigNum (9.99E+307) for finding last...

It is preferred to create your own thread if you are posting new requirement. You can provide reference to this thread, if it helps to define your issue.

You have not specified email client you are using. If you are using Microsoft Outlook then you can use excellent codes posted by Ron De Bruin. Below is the link: https://www.rondebruin.nl/win/s1/outlook/mail.htm

If you have Office 365 then you can do this without VBA as well. Following formula will produce the same result. =TEXTJOIN(" : ",TRUE,IF(ISNUMBER(SEARCH(A26,B17: P17,1)),B16: P16,"")) See attached Excel file. Note: I had to insert a space as the forum software changes cell address into smileys...

I am curious to know if you have tried approach like below. Try to run this by selecting the cells in the column. Dim rng as Range For each rng in Selection rng.FormulaArray = Replace(rng.Value,"ZZZ","=") Next rng

You can try with wildcard assuming G8 cell holds values like T2, T3 etc. See if below approach works for you. =IFERROR(VLOOKUP(G8&"*";Database!$B$2:$E$47;4;FALSE);0)

Third video in the series is launched today. I have specifically mentioned Chandoo.org :awesome: as one of the learning resource as a mark of respect to the great community we have here.