How to Programming with Excel-2013

the thing that I was looking for was an XML editor. I found more specific instructions on an Excel Add-in website. Thanks anyways.

Excel MATCH function partially working

excel,match,excel-2013

The Match() function has one important parameter: the last one. It is either 0 or 1. If it is 0, then Match will find only an EXACT match. In your scenario, when you convert numbers between different systems, the resulting number may not be an exact match. You may want...

Using conditional formatting with multiple conditions to color format cells

I was able to solve this problem by following the below steps: Home>Style>Conditional Formatting Create New Rule - "Use a formula to determine which cells to format" I had to create 5 different rules due to my session time setup. Each rule followed the basic setup: =AND($e11>=TIME(7,0,0),$e11<=TIME(9,59,59)) I only had...

PivotTable - Replace #N/A when using Show Value As

Right click on the pivot table and choose 'PivotTable Options'. You should see the following dialog: As indicated above, check the box saying "For error values show:" and put whatever you want in the entry box next to it. If you leave it empty, you'll get blanks....

Quickly Add New Series (Column) to Existing Graph - Excel 2013

vb.net,excel,excel-vba,ms-access-2013,excel-2013

One way: Click the chart and study the highlighted areas in the source data. Click and drag the corner of the blue area to include the new data. Another way: In the chart source dialog, change the chart data range to include the desired data. A third way: In the...

Fix for Windows 10 Excel 2013 Build 9926 Find and Replace freeze bug

excel-2013,windows-10

What happens if you use a VBA replace command? This doesn't give you a prompt so it may be a viable replacement. Cells.Replace "Test", "ATest" Maybe do a test first to make it more robust: If Selection.cells.Count > 1 then Selection.Replace "Test", "ATest" Else Cells.Replace "Test", "ATest" End if This...

Align Excel cell to center VB - xlCenter is not declared

xlCenter is a member of Microsoft.Office.Interop.Excel.Constants. Since you assigned Microsoft.Office.Interop.Excel to the name Excel, you can reference that constant like this ... xlWorkSheet.Range("H15:H16").VerticalAlignment = Excel.Constants.xlCenter ...

Taking data from other workbooks which vary in name - asking user to select them

excel,vba,excel-2013

Solved. For anyone who needs the solution, I worked it out by setting the directory default folder before each dialog box got opened. ChDir "C:\places\we\hide\things" Followed by the dialog box opening code. worked a charm, comment if you need a better explanation....

Identifying Context Filter in DAX Measure

excel-2013,dax

The reason why the [CurrentLevel] measure always evaluates to "Model_L1" when you have "Model_L1", "Business_L2", "Business_3" as the pivot table row fields is because the "Model_L1" sits above "Business_X" in the SWITCH() statement. This should give you the desired result: =SWITCH(TRUE(), CALCULATE(COUNTROWS(VALUES(DimAccount[Model_L3])), ALLEXCEPT(DimAccount, DimAccount[Model_L3])) = 1, "Model_L3", CALCULATE(COUNTROWS(VALUES(DimAccount[Business_L3])), ALLEXCEPT(DimAccount, DimAccount[Business_L3]))...

How can I get data from a Table [closed]

excel,excel-2013,data-validation

Maybe your google foo could do with some polish. For the dropdown try DATA > Data Tools, Data Validation, Allow: List Source: whatever the range is for the data in your table....

Excel VBA run-time error 1004 which I can't identify

excel,copy-paste,excel-2013

This code will fail if the Sheet containing the Defined Name has not been activated first!

How to copy Excel text and keep line breaks

Notepad doesn't interpret line breaks as cleverly as more fully-featured text editors. Your best bet is probably to try copy/pasting into either Wordpad, or to download something like Notepad++ (https://notepad-plus-plus.org/download/) and paste into that first. This answer does a nice job of further explaining the issue: http://superuser.com/questions/362087/notepad-ignoring-linebreaks...

How to Open and Parse Excel Data?

c#,excel-2013

This is lightening fast; I can't tell I'm not doing text manipulations with ReadAllLines and Regex. (Removed the text manipulation details). How I got it to work is explained in the comments to the original question. Not sure why it's so much faster than Interop.Excel. Inefficient coding? More efficient API?...

Object required error in Workbook_Open with For loop

excel,vba,excel-vba,excel-2007,excel-2013

Change the loop condition to verify cell instead of columnrange. Here is a simplified code that should work. For Each cell In columnrange If SelectData.DisplayData.Value <> cell Then cell.EntireColumn.Hidden = True Else cell.EntireColumn.Hidden = False End If Next cell ...

Get code runtimes via GetTickCount and format it in specific ways

This should get you the rudimentary results you are looking for. Sub myStopwatch() Dim t1 As Double, t2 As Double, et As Double, mssg As String Application.StatusBar = "Running..." Debug.Print "Start at: " & Time t1 = Timer ' do stuff here t2 = Timer Debug.Print "End at: " &...

Target.Validation.Type results in “Application-defined or object-defined” error

excel,excel-vba,excel-2013,data-validation

Here is one way. This checks if any cell in the sheet has validation. If it doesn't then it exits the sub. If it has then it checks if the current cell is part of those validation cells Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim r As Range Application.EnableEvents =...

Why can't I directly access table.AutoFilter.filters.Item(1).On and set it to False?

vba,table,filtering,excel-2013

For Excel 2010 and 2013 the AutoFilter.Filter.Ttem.On property is read-only. So that answers your first question. http://msdn.microsoft.com/en-us/library/office/ff197843%28v=office.15%29.aspx The same is true for the AutoFilter.Filter.Ttem.Criteria1 property. Which answers the second one. (I didn't know either of these things before now, so thanks for asking.)...

How to properly use OR within SUMPRODUCT function

Would not it be sufficient to write two separate SUMPRODUCT functions and sum-up their results? =SUMPRODUCT(....)+SUMPRODUCT(...)

Dropdown-List value calculation

excel,spreadsheet,excel-2013

I have tried it in Excel 2010, but I think there will be no significant difference in Excel 2013. Create a list of all eventually possible values (for example 0-1000). Assume that the values are located at H1:H1001. Choose a cell in which a threshold will be held. Suppose it...

How would one replace a certain character/symbol with an adjacent word/string?

Since you mention "a certain character" I take it your x is a literal so would suggest: =SUBSTITUTE(A1,"""x""",""""&B1&"""") in Row1 copied down. That is a similar approach to @xificurC's but would be more flexible for whatever lies on either side of "x"....

How to use UserForm Values for multiple things

vba,excel-vba,excel-2007,excel-2013,userform

As from the comments, I see you don't need anymore to know about points 1 and 2, I will hence limit my answer to the point 3. Last Part of this question if I have a Textbox in the userform that contains a date in the format 1/18/2015 is there...

Create Bar Chart in Excel with Start Time and Duration

excel,time,charts,excel-2013

Charting differs between different version of Excel and if not using something like VBA is probably best asked about on Super User if not already a question asked there (answers/guidance there may be more detailed than on SO) but since you clearly have a good understanding already maybe an example...

Mark items in Excel based on criteria

java,comments,excel-2013,crawler4j,jericho-html-parser

You can use the INDEX function to get the information from a particular set of coordinates. For example, in this case, we'd get the value for the cell in the 5th row and the 1st column of the selected area: If we want to dynamically find those values, we can...

Combining matrices to produce one matrix

sql,sql-server,excel,matrix,excel-2013

Unfortunately, can't help with the formal terms you've asked for but (IMO) for such a small dataset it matters not and brute-force can easily be your friend. I would suggest UNPIVOTing each of your 3 datasets to get datasets you can more easily work with {Gender,Area,Reqs}, {Ageband,Area,Reqs}, {SEG,Area,Req} and simply...

How to retrieve all the user comments from a site?

Yes it is possible. Start crawling on your search site (http://www.consumercomplaints.in/?search=chevrolet) Use the visitPage method of crawler4j to only follow comments and the ongoing pages. Take the html Content from crawler4j and shove it to jericho filter out the content you want to store and write it to some kind...

Opening CSV File

excel,csv,ms-office,excel-2013

You can solve the problem by inserting the following simple text at the beginning (the first line) of your .csv file: sep=; This will not be seen when the file is opened in Excel. What it will do - it will explicitly tell Excel that the delimiter is ;, and...

Why would ReDim throw a Subscript out of range error 9

excel-vba,dynamic-arrays,excel-2013

Examples of invalid array subscripts would be non-integer or negative subscripts. If intQuantity = 0 at any point in your program, then that would produce a subscript for your array of -1. Similarly, if getCutQuantity returns a non-integer, then your array subscript would be a non-integer. Glad that helped.

excel Calendar and HYPERLINK

It should be possible, but I don't understand everything you've got going on in either forumlas. The format for a hyperlink formula is =HYPERLINK(Link_location, Link_word) so you would put the location your linking to in the first part, then you would encase your date formula in parentheses for the second...

Having Trouble Running a Simple Blank Row Deleting

Try this- make sure to change Sheet1 to what ever sheet you are working on. Tested Excel 2010 Option Explicit '// Delete blank Rows Sub xlDeleteBlankRows() Dim xlWb As Workbook Dim i As Long Set xlWb = ActiveWorkbook For i = xlWb.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).row To 1 Step -1 If WorksheetFunction.CountA(xlWb.Sheets("Sheet1").Rows(i)) = 0...

Excel VBA - Refresh selected queries / connections

vb.net,excel,excel-vba,excel-interop,excel-2013

You need to refer to the ListObject by name, rather than the connection, and then access its Querytable property: With wks.Listobjects(str).QueryTable ...

Range in .Select() not working on xlApp.ActiveWindow.FreezePanes

xlApp.Range("A4").Select Then apply the freeze panes....

Creating a “color scale” using vba (avoiding conditional formatting)

excel,vba,excel-vba,excel-2007,excel-2013

I've managed to find the correct answer, it's actually rather simple. All you have to do is add conditional formatting and then set the .Interior.Color to the same as what the .DisplayFormat.Interior.Color is and then delete the conditional formatting. This will do exactly what is requested in the main post;...

Combining multiple IF/AND/THEN statements Part 2

vba,excel-vba,excel-2010,solver,excel-2013

You can just add that first, in this generic form =IF($D11="","",your_formula) so specifically that becomes: =IF($D11="","",IF(OR(AND($D11="Long",$M11>=$N11),AND($D11="Short",$M11<=$N11)), "Win","Loss")) see screenshot ...

Setting the convergence value to a variable for the built-in solver

Convergence, and also all other SolverOptions can be set using cell values, for example Convergence:= Range("A1").value. Value2 is a little faster than Value, and it is good practice to explicitly cast to double the cell value (and also trap any errors). Another way is to add the values as named...

Object Defined error in Excel VBA script

excel,vba,excel-vba,excel-2010,excel-2013

Very likely sht is null. You Dim sht as Worksheet but never Set it to anything. The line of your error is the first line which uses sht so it just happens to be the place where the error is brought to your attention. I would thin you would want...

Passing an integer argument in VBA

1). You have declared Public var: Public RowNumber As Integer so remove the local declaration of the same var in Sub Dim RowNumber As Integer 'remove 2). Regarding your second issue on 'how to pass the argument", refer to the following example demonstrating two options of passing arguments to Sub...

excel SUMPRODUCT with SUMIF and INDIRECT gives #REF! error

There are a couple of issues here. I'm not sure you need to use INDIRECT at all. The way I understand your question, your sheet names are K1, and K2. Not that your sheet names are located in cells K1 and K2. So you should be able to reference those...

Array Formula for text search

You need to provide an extra layer of processing so that the formula does not halt at the first result of the ISNUMBER function. A SUM function wrapper should be sufficient. =SUM(--ISNUMBER(SEARCH(G2:G7, A1))) Array formulas need to be finalized with Ctrl+Shift+Enter↵. Any non-zero result will mean that at least one...

VBA Centre Userform On Active Screen

excel,excel-formula,worksheet-function,excel-2013,countif

I just wanted to post my working solution, which building upon what I'd alreafdy written, a work colleague was able to finish. The code is as follows: Private Sub UserForm_Initialize() Me.BackColor = RGB(174, 198, 207) End Sub and Private Sub Workbook_Open() Dim j As Integer 'Display the splash form non-modally....

COUNTIF with OR Statement

Ugly, but might suit: =COUNTIFS(E:E;">120";E:E;"<139")+COUNTIFS(F:F;">80";F:F;"<89")-COUNTIFS(E:E;">120";E:E;"<139";F:F;">80";F:F;"<89") Counts all the applicable instances in ColumnE, adds the count of all the applicable instances in ColumnF then subtracts the instances where both ColumnE and ColumnF are within the respective bounds - this last part might not be required. An array formula might be more...

Linking correctly multiple criteria formula to another worksheet

vb.net,ribbon,excel-2013,excel-dna

After reading the comments provided by @RonRosenfeld, I can see that the correct way to write this calculation is as follows... =SUMPRODUCT(--('Sheet1'!$E$11:$E$61>='Sheet1'!$AE$5),--('Sheet1'!$E$11:$E$61<='Sheet1'!$AF$5),--('Sheet'!$H11:$H61='Sheet'!$D$330)) Making sure that EACH element of the calculation that is being referenced is led by the appropriate page name and !....

Invalidate ribbon control in Excel 2013 for multiple workbooks

The Invalidation is only processed for the active workbook's ribbon. But when you then switch to another workbook, the callbacks will fire again and will now be applied to the ribbon of the new workbook. There's a bug and some quirks in Excel 2013 related to this switching: If you...

Conditional Excel formula based on existing value in adjacent cells?

excel,excel-vba,excel-2013

Try this in cell D2: =IF(COUNT(FIND("@",C2))>0,"Certain Text","")...

Use string variable to set object variable in VBA? (Excel 2013)

The CommandButton belongs to an OLEObject try ButtonString = "CommandButton1" Set ButtonObj = ActiveSheet.OLEObjects(ButtonString) ButtonCaption = "Something" ButtonObj.Object.Caption = ButtonCaption 'example of the kind of parameters I want to change Note that some properties occur directly under ButtonObj, others such as Caption sit below Object ...

Activate worksheet using excel VBA

excel,autohotkey,excel-2013

is it possible to solve this by using Like or Contain statements? From your comment, yes. After opening the workbook, iterate the worksheet collection like this: Dim sh As Worksheet For Each sh In wb.Sheets If InStr(sh.Name, "WorkSheet_A") <> 0 Then sheet_Name = sh.Name: Exit For End If Next Or...

Object Required Error after upgrading to Office 2013

vba,excel-vba,excel-2013

Apparently the Past method must be returning an array of ShapeRanges. I'm not sure if this is how it's always been and Office 2010 was a little more forgiving or not. So, to correct for this issue, when referencing sr I've had to do like sr(sr.Count). Working code below... Sub...

Autohotkey F4 script does not work on Excel 2013

I've figured out a way here, thanks for the comments. By using the $ operator, we can then use the Send method, which forces the active application to override its default keys and receive the corresponding keystroke. Here's the workaround $#4:: #IfWinActive ahk_class XLMAIN Send {F4} return #IfWinActive ...

Hyperlink Screen Tip

c#,excel,excel-2013,conditional-formatting

From a quick play around it seems as though you need to be using ScreenTip rather than TextToDisplay if your goal is the hover over text. For the section below: ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, 2), Address:="", _ TextToDisplay:=CStr(iRow - 16) Try changing it to: Activesheet.Hyperlinks.Add Anchor:=Cells(iRow, 2), Address:="", _ ScreenTip:=CStr(iRow - 16)...

Excel add traffic light

Here is a sample code that I quickly wrote to demonstrate using the Traffic Lights in conditional formatting. Please change it to suit your needs. using System; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; Namespace WindowsFormsApplication2 { public partial class Form1 : Form { Public Form1() { InitializeComponent(); } private void...

VBA Error Handling to check for folder existence

All, thank you for your help and guidance. I just wanted to let you know that with help from a work colleague, I've come up with the code as shown below. Many thanks and kind regards. Chris Private Sub Workbook_Open() Dim j As Integer Dim fPath As String On Error...

delete blank rows on an excel worksheet, in a different excel workbook, using vba

validation,excel-vba,excel-2013

This is very easy to do.. Sub RemoveEmptyRows() ' this macro will remove all rows that contain no data Dim file_name as String Dim sheet_name as String file_name = "c:\my_folder\my_wb_file.xlsx" 'Change to whatever file you want sheet_name = "Sheet1" 'Change to whatever sheet you want Dim i As Long Dim...

Fill Excel Table with VBA Field by Field

excel,vba,excel-2013

A ListObject table is comprised of a HeaderRowRange (if the table has headers) and a DataBodyRange. Both are Range objects that you can iterate using normal iteration methods for cells: Dim r as Long, c as Long For r = 1 to Tbl.DataBodyRange.Rows.Count For c = 1 to Tbl.DataBodyRange.COlumns.Count Tbl.DataBodyRange.Cells(r,...

Validation.Type throws error 1004

This occured because I had updated the error message of the data validation in one cell but not the other. When I copied and pasted the error message into the data validation of the other cell in the range, this problem went away. Conclusion: Make sure that the data validation...

Change excel default cursor to arrow pointer

vba,excel-2013

To get the normal arrow cursor, use: Application.Cursor = xlNorthwestArrow The [+] cursor is the default, so you have to call the arrow cursor specifically....

Using COUNTIFS formulas when linking worksheets

excel,vba,excel-vba,hyperlink,excel-2013

You need to include the sheet name in all the ranges, i.e. =COUNTIFS(Input!H11:H61,"YES",Input!Q11:Q61,"Win")...

VBA retrieve hyperlink target sheet?

SubAddress is what you want: Sub Test() Dim hl As Hyperlink, r As Range Set hl = ActiveSheet.Hyperlinks(1) Set r = Application.Evaluate(hl.SubAddress) Debug.Print "Sheet: '" & r.Parent.Name & "'", "Range:" & r.Address() End Sub ...

Office 2010 Shared PIA is not Available?

visual-studio-2010,excel-2010,excel-2013

It took a while to figure out but its done finally. The add in launch conditions were checking for the availability of the shared PIA. Condition: HASSHAREDPIA and it was looking for the corresponding component id associated with excel 2013 PIA component ID Click Here https://social.msdn.microsoft.com/Forums/vstudio/en-US/1fd8690a-812c-49f9-a77e-e19f24de7c4e/office-2013-pia-component-ids?forum=vsto to obtain the suitable...

How do I count how many occurrences per week something happens excel?

excel,date,excel-2013

Just Like TMH8885 metioned get week number from each date and then assuming that your dates are in Column A and weeknumbers are in column B make a table that list numbers from 1 to 52 next to which enter a COUNTIF(B:B,D2)(change D2 to cell pointing at correct week number...

Can Not Get My VLookUp In Excel To Return The Requested Data

excel-formula,vlookup,excel-2013

Try using the Index Match method. It's an alternative to Vlookup which doesn't require data to be sorted and can therefore be of more use. The typical structure of this method is (the text inside the asterisk will give the ranges specific to your sheet: =INDEX (**Column from which you...

Variable Declaration Lists

excel-vba,variables,excel-2013

You can do it like this: Dim wbTHMacro As Workbook, wsRegulares As Worksheet, wsRegularesDemitidos As Worksheet Dim wsTempActivos As Worksheet, wsTempJA As Worksheet, wsTempFit As Worksheet Dim wsTempDemitidos As Worksheet, wsPresenceSystem As Worksheet, wsResultados As Worksheet Dim wsDLList As Worksheet, wbRegularesBruto As Workbook Personally, I rather like to declare my...

Detect autofilter on Excel Tables

excel,excel-vba,excel-2013

activesheet.listobjects(1).showautofilter will be True if the Autofilter controls are showing....

Sort entire Pivot table in Excel 2013 by column

sorting,excel-2010,pivot-table,excel-2013,powerpivot

If you have more than one field in the row area of the pivot table, you cannot create a sort purely by value. The hierarchy cannot be ignored. That's how a pivot table works. Don't shoot the messenger.

Adding a “count” when pasting

Select the three cells. Find the fill handle. It is the little square at the lower right corner of the selection. Click and drag it down to row 500. ...

How to pass a list of sheets to a VB Sheets.Select

You are better off moving the worksheet names to a pre-defined string array after you physically remove the double quotes like this: 'Define a string array for the list of worksheets Dim arrSheets() As String ... ... Print_Sheets = Worksheets("ControlSheet").Cells(16, "G").Value 'Remove the double quotes from the delimited list of...

Loop through directory, open workbook, print selected worksheets

excel,vba,excel-vba,excel-2007,excel-2013

I see two problems Where are you opening the workbook? You need to move StrFile = Dir just before the Loop. With this command you are telling it to find the next file. Try this (UNTESTED) Sub Loop2() Dim StrFile As String Dim WSCount As Integer Dim sh As Worksheet...

PowerView automatically aggregating data

excel-2013,powerview

Power View will automatically aggregate numbers, there are two options to prevent this. In the Power View field well fields can be set to 'do no aggregate', alternatively fields can be prevented by aggregating at all via the Power Pivot window. Do not summarize via the field well Do not...

Combining multiple IF/AND/THEN statements

excel,excel-2013,forex

Maybe something like: =IF(OR(AND(D11="long",M11>=N11),AND(D11="short",N11>=M11)),"Win","Loss") ...

VBA Copy Destination but as values

Since you want to avoid the clipboard and only copy Values, you can use assignment to the Value property instead of Range.Copy Something like this Sub Demo() Dim Sheet As Worksheet Dim FoundLocationSheet As Boolean Dim n As Long Dim rSource As Range Dim rDest As Range Dim AllSheet As...

excel active x control button is not working suddenly [duplicate]

excel,activex,excel-2013

https://social.technet.microsoft.com/Forums/office/en-US/94d0b004-3303-421d-9ed2-351683b89d08/sudden-problems-with-inserting-activex-basic-controls-such-as-command-buttons?forum=officeitproprevious found out the solution. Quit Excel. Start Windows Explorer. Select the system drive (usually C:). Use the Search box to search for *.exd Delete all found files. (Thanks to Excel MVP RoryA for this tip) Regards, Hans Vogelaar (http://www.eileenslounge.com)...

Check if cell contains Non-Alpha characters in Excel

excel-2013,conditional-formatting

You can use 26 nested SUBSTITUTEs to remove all alphabetic characters from the text. If anything is left over, the cell contains non-alpha characters. And thanks to @RaGe for pointing out that you need to check for empty cells as well:...

Conditional Formatting for Filtered area

Please try HOME > Conditional Formatting, New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:: =ISODD(SUBTOTAL(3,INDIRECT("A$1:A"&ROW()))) Format..., select formatting of choice, OK, OK....

Copy/Paste dynamic range

vba,excel-vba,offset,excel-2013

Always use Option Explicit at the beginning of every module to prevent from typos. Always! You had typos at the bottom - Colum1 and Colum2. Avoid Activate and Select (you had Sheets("DATA").Activate) - better performance, smaller error chance. Instead, you should always explicitly tell VBA which sheet you are...

Finding ID based on first and last name

excel-vba,lookup,excel-2013

Try this for the next button Private Sub NextButton_Click() Dim emptyRow As Long Dim matchFound As Boolean Dim matchCount As Long Dim matchRow As Long Dim i As Long 'Determine the first empty cell in column E If Cells(1, 5).Value = "" Then emptyRow = 1 Else emptyRow = Cells(Columns(5).Rows.Count,...

VLOOKUP not returning for datetime

excel,vlookup,excel-2013

Welcome to the world of time, base60 vs. base10 and 15 significant digit floating point precision. I find that sometimes it is simply expedient to give yourself a 'time window' to meet rather than try to round off ranges. The formula in E2 is, =SUMIFS($B$2:$B$8, $A$2:$A$8, ">"&D2-TIME(0, 0, 2),...

Excel : Delete duplicate occurrences based on ColumnA & ColumnB values.

excel-formula,excel-2010,frequency,excel-2013,countif

no need to formula, select the columns in which you are looking for the duplicates then go to your DATA tab in excel, and click on remove duplicates in the data tools section. There you go! ...

Can't get OnUndo to run available macro in ThisWorkbook module [duplicate]

Should work if you place the code in a regular module, not in the ThisWorkbook module.

repeating a formula conditionally in excel 2013?

Try this in C1, =--NOT(A1<OFFSET('Different Sheet'!$B$1, MOD(ROW(1:1)-1, 2), 0)) Fill down as necessary. You only seem to be looking for a 0 or a 1 so I've simplified your IF statement. (Note: maths with MOD adjusted for more universality) For different multiples you should only have to change the divisor...

Concatenate based on cell value

excel,table,pivot-table,excel-2013,conditional-formatting

However the result may be achieved with formulae. With a PivotTable (in case the raw data may not be sorted) with Status and Emp for ROWS and Achievement in VALUES and (for layout as in image) these formulae copied down to suit: in I2: =F2&" = "&TEXT(G2,"0.00%") in J2: =IF(E2<>"",I2,J1&"...

Multiple criteria conditional formatting in a PivotTable

Select the column to be highlighted and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true: =G1>F1 with red colour =G1<F1 with green colour leaves equal values without highlighting. If bothered by highlighting of...

How to change string case in Excel 2013

excel,excel-2010,excel-2013

Select a string (highlight). Go to menu and start recording a macro. Do necessary action (ok that is nebulous). Stop recording macro. Assign hotkey. Save macro. Now next time, highlight something. Hit hotkey. Note that later you can go in and modify the macro VBA code that you find for...

Converting Date Into a specific text format in excel

excel,excel-formula,excel-2010,excel-2013

You could simply turn around your first example and use the correct string: =TEXT(J3, "yyyyMM") Note, I used upper case M. Lower case m means minutes, while upper case M means month. The CONCATENATE is unnecessary. Please also note, that this is prone to localization issues, i.e. it depends on...

How to turn a range value to a double (VBA)

vba,excel-vba,excel-2013

I put the following in a standard module: Function IfMissionarySupplies(Missionary_Type As String) As Double Wb = ThisWorkbook.Name 'Wb is a global variable. Dim Elder_Supplies, Sister_Supplies As Double Elder_Supplies = Workbooks(Wb).Worksheets("Control Variables").Range("C11").Value Sister_Supplies = Workbooks(Wb).Worksheets("Control Variables").Range("C14").Value If Missionary_Type = "Sisters" Then IfMissionarySupplies = Sister_Supplies Exit Function ElseIf Missionary_Type = "Elders" Then...

Proper use of AND and OR within IF statements

AND, OR and IF are all functions not just statements. You have 2 sets of conditions which are valid so put the ANDs inside the OR: =IF(OR(AND(D11 = "Short", G11 > 0), AND(D11 = "Long", G11 < 0)), "YES", "NO") ...

VBA Open URL's in Excel 2013, 64bit

excel,vba,excel-vba,excel-2010,excel-2013

If you're using an API in 64-bit office then you may need to declare it as 'pointer safe' and use the VBA7 LongPtr type. You can use conditional compilation to test the environment using the VBA7 Win64 constant: #If Win64 Then Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"(...

Data is graphing in Excel 2010, but not in Excel 2013

Replace With sc.NewSeries with With cht.SeriesCollection.NewSeries I had the same issue in Excel 2013. I don't know why, but this solution works for me....

Excel, SUMIFS criteria range is also sum range

excel,excel-formula,excel-2010,excel-2013,sumifs

@Fabricator's answer should work, but if you want to write it with just SUMIFS, you need to quote your conditions and concatenate your values with &. Excel doesn't have any problems with a column being both a condition / the summing column. =SUMIFS([anotherFilel.xlsx]sheet!$I$16:$I$99999, [anotherFilel.xlsx]sheet!$I$16:$I$99999, ">" & $B$13, [anotherFilel.xlsx]sheet!$H$16:$H$99999, "<=" &...

Cancelled Save De-activates Links

vba,excel-vba,printing,excel-2013,page-size

The run time error stems from trying to access fldr.SelectedItems(1) if the user cancelled the dialog. All you should need to do is check to see if you got a folder back: Dim fldr As FileDialog Set fldr = Application.FileDialog(msoFileDialogFolderPicker) fldr.AllowMultiSelect = False fldr.Show 'Did the user cancel? If fldr.SelectedItems.Count...

Excel VBA: User defined function based on checkbox input not recalculating

excel,vba,excel-vba,checkbox,excel-2013

I'm not sure that you are talking about a CheckBox in a UserForm, but if you are just place this line in the CheckBox_Change() : Sheets("Your_Sheet_Name").Calculate And if it is an Excel CheckBox : Add a linked cell (here is in C1) Place this in the Worksheet module This code:...

Choosing paper size (NOT DEFAULT sizes) in excel vba

Question 1 xlPaperUser is a User-Defined paper size that is assigned a constant value of 256. If this has not been defined, it may throw an error. Question 2 There is no way to create custom paper sizes in Excel, however you can create custom paper sizes on many printers....

Excel - How to concatenate 2 values to make a refference

Commenting in an answer so as to add a picture. But your second formula also seems to work fine: Note that with a 2 in B3, the formula returns B from Sheet_2!B2...

Pulling data out of an worksheet by country

This is doable for a couple thousand rows of data. If your 'huge amount of people' is much more than that, an advanced filter or pivot table is a more viable solution. With UK in D3 use the following in E3. =IFERROR(INDEX($B$2:$B$9999, SMALL(INDEX(ROW($1:$9995)+($A$2:$A$9996<>D3)*1E+99, , ), ROW(1:1))), "") Fill down...

Change font on styles in MS Excel 2013

excel,excel-formula,excel-2013,countif,excel-indirect

Well, as I started typing this up, I figured it out (mind you, this has been driving me nuts for months now). Right click on the style and unselect the Font option. This will allow you to use your default font. It doesn't give you the option to specify your...

Dynamic formula with quotes inside a COUNTIFS function

The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells. =COUNTIFS(cancellations!AG2:AG408,">0",cancellations!AG2:AG408,B1) should work where B1 contains...

Trying to use countif with multiple criteria involving a time range and a set amount [duplicate]