Search results

Hi, Try the following steps: Select your cell F5 till the last cell you want to do Data Validation. Go to Data > Data Validation Setting tab > Select Custom, and enter the following formula: =VLOOKUP(E5,PM!$B$4:$C$100,2,0)<>0 Error Alert tab > Error Message: Qty is Zero (or any message) Click...

Nice Formula Bosco.

Hi Amit, Please check this {array formula} =IFERROR(INDEX($B$4:$B$35,SMALL(IF(ISNUMBER($B$4:$B$35),ROW($B$4:$B$35)-MIN(ROW($B$4:$B$35)-1)),IF(ISERROR(B4),COUNTIF($B$4:$B4,">0")+1,COUNTIF($B$4:$B4,">0")))),"") {array formula needs to be entered with Ctrl+Shift+Enter} Regards,

Hi, Or this with just Enter: =IFERROR(AGGREGATE(15,6,1/(($B$1:$F$42=$B45)/ROW($B$1:$F$42)),COLUMNS($A1:A1)),"") Regards,

Hi Erik, Have you tried IsEmpty in vba? Regards,

Congrats Bosco, Keep Rocking ;) Regards,

Hi David, Let me try: 1) =IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded")) First IF is checking if A2 is blank then leave the result to blank 2nd IF is checking the count of word "vacation" for particular ID, if...

Hi David, See the bold Red part updated: =IF(A2="","",IF(SUM(COUNTIFS(A:A,A2,B:B,{"vacation","sickness"})),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded")) Regards,

Hi David, See if these works: 1) =IF(A2="","",IF(COUNTIFS(A:A,A2,B:B,"vacation"),"Yes Vacation or Sickness Recorded","Only Work Recorded - No Vacation Recorded")) 2) =IF(B2="Work","",SUM(SUMIFS(C:C,A:A,A2,B:B,{"vacation","Sickness"}))) 3) =EDATE(D2,12-MONTH(D2))-D2 Regards,

That wasn't mentioned in initial post. Anyways, take care and good luck. Regards,

Hi Tripp, Modified the code available here: https://www.thespreadsheetguru.com/the-code-vault/2014/4/23/loop-through-all-excel-files-in-a-given-folder Sub LoopAllExcelFilesInFolder() 'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them 'SOURCE...

Hi, I have slightly modified the code and tested with random data (as per attached) Wait for vba experts, if it still doesn't work. Regards,

Hi, See if this works: Sub Copy_H() Sheets("Annual_Leave_Record").Range("D5:Q30").Copy Destination:=Sheets("Sick_Leave_Record").Range("D5") ThisWorkbook.Sheets("Sick_Leave_Record").Activate Range("D5:Q30").Select Selection.FormatConditions.Delete End Sub VBA is not my cup of tea. Regards,

Hi to all, One more with just enter: =IFERROR(INDEX($A$2:$A$51,AGGREGATE(15,6,1/(($B$2:$B$51=$E$1)/(ROW($A$2:$A$51)-1)),ROWS($1:1))),"") Regards,

Hi Chihiro, Great, this must be the quickest 5k post. Looking forward to learn more from your insightful post. Regards,

Hi, Can be done via PQ, please share sample file. Please note: it will require refresh every time the data is changed. Regards,

Hi Hui, Wow awesome... I just checked mine, looking great. Mind blowing.

Hi, May be this: =TEXT(ABS(A2),"y"" years ""m"" months ""d"" days""") Regards,

Hi Peter, Yes you are right, cell need to be wide or merged to show complete text and date. But using custom formatted cells we have advantage if the cell needs to be used for further calculations.

Or use the custom format: "For the month of "mmm-yy Regards,

Unnecessary poll removed

Hi Adeel, Range refers to all the cells e.g A2:A10 from which you want to count Criteria is the condition that defines which cells will be counted. Normally we use single cell for criteria say B2, but in some cases we have to use the range in Criteria then we use A2:A10, this turn the normal...

Hi David, Please share a sample file. Also please note the IF is not necessary. Regards,

Hi, I think Title edit option available to Mods only, I have updated your post title to [Solved] Take Care,

HI, May be this {array formula} =SUM(IFERROR((INDIRECT(D11))*(A:A=INDIRECT(D10)),0)) {array formula needs to be entered with a key combination of Ctrl+Shift+Enter} Note: it uses full column reference, it may slow down your sheet. Regards,