Excel-Buzz

Hi Everyone, The situation is very clear from the picture. The aim is to search F2:H2 (Name, Team, Jersy No) collectively in the A1:C7 table and return corresponding Goals.I have used the following formula:- =INDEX(D1:D7,MATCH(3,MMULT(--(A1:C7=F2:H2),TRANSPOSE(COLUMN(A1:C1)^0)),0)) The formula is An Array formula. So use CTRL + SHIFT + ENTER to confirm it. I will start by explaining the following part:- MMULT(--(A1:C7=F2:H2),TRANSPOSE(COLUMN(A1:C1)^0))

Hi Everyone, As you can see in the picture, is the situation which I came across while serving at ExcelForum.com, So I decided to explain the work around. Actually, what I am doing is, Listing Unique Fruits for Smith and Jones in a Drop Down. I have used Named Ranges to make it more understandable. Here are the steps:- Making some Dynamic named ranges. Note:- While Making these Named ranges, you have to select the any cell in the first row. This is Important as behaviour of the Named Ranges Depends up on that. 1. Name =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",100),Sheet1!$A:$A,1)) 2. Fruit =Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(REPT("z",100),Sheet1!$B:$B,1))

We generally come across situation where we want to list Unique rows (for ranges having more than one column) or Unique cells (for ranges having one column). Here is the Simple Macro for that. Sub AdvancedFilter() 'Declaring Variables Dim Des As Range 'Destination Variable Dim Sou As Range 'Source Variable Set Des = Range("G1") 'Set Destination Cell here Des.CurrentRegion.ClearContents 'Clearing Previous Content Set Sou = Range("A1").CurrentRegion.EntireColumn 'Set Source Range here 'Using Advanced filter Sou.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Des, _ Unique:=True Des.CurrentRegion.EntireColumn.SpecialCells(4)...

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.

Hi one and all, This time I have come up with a code that will Save a Copy of your current Excel file on a Specified Location (with Data & Time as Prefix) after an Specified Interval. You can always change the Folder Path and Interval Time.  Remarks:- As it involves macros, so make sure that you save the file in .xlsm ( Macro Enabled Format ) and also to enable macros after while Excel Workbook. So here are the steps:- 1. Open the Workbook. Press Alt + F11 to open Visual Basic Window. 2. On the Left Side of the Screen, there would be a Thisworkbook Module. Double Click it. 3. Now Copy and Paste the code below there:- Private Sub Workbook_Open() Call Ontime End Sub

Hi one and all, This time I have tried to Explain the Frequency Function. 1. How Frequency Function works with Non Duplicate, Ascending Bin Array Values Frequency Function calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula. Syntax FREQUENCY(data_array,bins_array) Data_array   is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros. Bins_array   is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

Hi One and all, This time I have come up with a macro which will disable the Saving As functionality of the Excel Workbook. By using this macro, you can restrict user to use F12 or SaveAs button to save the current progress. Thus allowing only Saving (Ctrl + S) the file. Here are the steps:- 1. Copy the Code. 2. Open the Excel Workbook. 3. Press Alt + F11 to open VB Window. 4. In ThisWorkbook Module, Paste the code. 5. Close VB Window. 6. Try Pressing F12 and Clicking Save As button in the File menu, it won't be working any more. 7. Macros should be enabled for this to work. Here is the Code:- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then Cancel = True End Sub Regards, Check the attached file:- SaveAs Disabled.xlsm