Search results

In the formula =Index(range1,small(if(range2=cell,row(range2)),row()) when dragged gives me the first cell match, second cell match & so on.... is it possible to get match that begins with or contains...something like =Index(range1,small(if(range2=cell&"*",row(range2)),row())...

Hi Friends, I have three range of data, lets say C2:C40, E2:E40, G2:G30 through some formula this range gets populated (a few cells goes as blank ""). I was wondering if someone can help me get some UDF (again UDF) to get the unique values from this range in I2:I40... Image link...

Firstly, this isn't exactly an excel question but related to help in creating a RAG/Dashboard stuff in excel. Background: The boss just called me in and explained the following - We have plans to migrate some work from onshore for which we will be hiring candidates. We have the actual...

Hi, I am testing the below code through F8 option, however each time I reach on Workbooks.open the further code runs automatically (without pressing F8) and ends... Any ideas....I want to go through F8 F8 to check in each of the workbook. Sub update() Dim myfolder, myfile As Variant Dim...

INDEX('Report'!$B$1:$B$25000,SMALL(IF(('Report'!$M$1:$M$25000="Health")*('Report'!$P$1:$P$25000="Open"),ROW('Report'!$B$1:$B$25000)),ROW())) I want to add an or condition wherby, if in P col is Accept as well - taken in account. any help please. something like...

Hi Please dont beat me for asking this. I have a lot of data, with the help of chandoo highlight series link, I’m able to get the count within the data for parameter on which mouse is rolled. However I would also like to filter the actual data based on mouse rollover, something like Public...

I have received a .xlsm file, hereby there is a lot of coding....I am going through step by step for each by using F8 key, When the first time I press F8 there is a "Public po_QNode As SmartArtNode" declaration (i think) which gets highlight and throws the above error.... After googleing &...

Hello Experts..... So my boss wants to see the Traffic Lights condiotnal formatting in the report....(why did I showed him)... Now when I input "R", "A" or "G" in the cell, instead of the text it should show the proper traffic light.... Any ideas...how to apply it on text.... VBA...

Hi, I accidentally shift deleted a xlsm file (I need to get rid of this habit)....anyway I wanted to recover it. By using some software I have recovered it, but now it not opening... I've attached the link to the file....if anybody can help....I have spent almost 48 hours trying to open it...

I have five different users using different workbooks (front end); at the end of day they need to click “update database macro” which compiles all their data in a different workbook (back end). Sub add_to_master_sheet() If MsgBox("Update Database to reflect data from this file?", vbYesNo...

Sub chart_data() ActiveSheet.ChartObjects("Chart 2").Activate For m = 1 To 13 ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(m).Name = n 'Range("B28:B40") want this range looped ActiveChart.SeriesCollection(1).XValues = d 'Range("A43:A55")want this range looped...

Nothing important...just because it looked good to me....is it possible to prepare such a kind of chart in excel. http://www.viewtific.com/wp-content/uploads/2013/02/Pew-EPI.png

I have got the following code from http://excel.tips.net/T002668_Always_Open_at_100_Zoom.html The code works fine on 2007 but throws an error in 2010 version,any reason or help. Declare Function GetSystemMetrics32 Lib "user32" _ Alias "GetSystemMetrics" (ByVal nIndex As Long)...

Sub run() Sheets("Pivot 2").PivotTables("Pivottable1").PivotFields("Date").ClearAllFilters For i = 1 To 5 Sheets("Pivot 2").PivotTables("Pivottable1").PivotFields("Date").PivotItems(i).Visible = False Next i End sub The above code is working great with strings/text but isnt working on...

The below code works well in 2007 but throws an error in 2010. The reason I believe is 2010 don't provide the option for "Enable Macro" and stores it in trusted places. Whats the alternative for this... In workbook Private Sub Workbook_Open() Application.ScreenUpdating = False...

Hi, I am using this vba, its working fine till the time save option button is clicked but when the users click on the Red cross button, save alert is displayed, I click NO the wb closes without saving, I click cancel, thats fine (I want this option), I click YES ("Thats were the problem is)...

How can I make this code small.... Private Sub Calendar1_Click() 'Calendar value in textbox UserForm1.TextBox13.Value = Format(Calendar1.Value, "dd/mmm/yyyy") Calendar1.Visible = False End Sub Private Sub Calendar2_Click() 'Calendar value in textbox UserForm1.TextBox14.Value =...

I have a userform for data entry, the sheet is protected. Macro in userform unprotect sheet and add the entries in userform to worksheet and protect it again. Now this is supposed to be a shared sheet were different users will enter data in different sheet from respective userforms. Now the...

How can I create a function/add in for this formula ={INDEX(B:B,SMALL(IF($A$2:$A$10 =$D$2,ROW($A$2:$A$10)),ROW(A1)))}

Hi, I have a rounded rectangle shape, I have given a hyperlink to it (same workbook, different sheet), I also need to run a macro (its with me) from the same shape...however only the hyperlink criteria works when I click the shape the macro doesnt run....any assistance... I tried using...

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/ In the attached link - part Scenario 2: Sum all values within a 2D Range matching 2 criteria is there by any mean as we double click (sold) and get the 3-May data in a different sheet as we get in a pivot. If possible, please...

Hey Friends, Any idea as how would this work =SUMPRODUCT(--(A1:A19=G1:G3))

Hi, In pivot table 2003, while preparing the layout there's a column/field as "PAGE", the data inputted in this field have options when double clicked to select/unselect values to be showed/displayed in pivot (Report filter in 2007). I am not able to find this feature in 2007 (of deselecting...

If Me.OptionButton1.Value = True Then Sheets("Sheet1").OptionButton1.Value = True Sheets("Sheet2").OptionButton1.Value = True Sheets("Sheet3").OptionButton1.Value = True Sheets("Sheet4").OptionButton1.Value = True Sheets("Sheet5").OptionButton1.Value = True End If End Sub can this be...

Hello, As you can see in the below code, screen starts flickering as soon as a copy of sheet1 is created, but as I delete this sheet after the print, I don’t require it – not even want to give a hint about how the macro performs. Any help will be appreciated. Sub Macro1()...