Tag Info

New answers tagged

0

You can get this behavior with just a single mouse click. Include the following event macro in the worksheet code area: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Application.EnableEvents = False Application.SendKeys "%{DOWN}f" Application.EnableEvents = True ...


0

When in Excel shared files, sometimes the users do not close out properly before shutting down etc. Excel thinks they are still connected. If you go to - View, -Share Workbook, it will list all the users, If you know for a fact that no one else is in it, you can remove them. This will not remove them from using the workbook in the future, it just removes ...


0

See if this helps: Sub parse_data() Dim lr As Long Dim ws As Worksheet Dim vcol, i As Integer Dim icol As Long Dim myarr As Variant Dim title As String Dim titlerow As Integer vcol = 1 Set ws = Sheets("Sheet1") lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row title = "A1:C1" titlerow = ws.Range(title).Cells(1).Row icol = ...


0

I had a similar problem in a template. I solved it by deleting the sheet which had, at one point in time, had a pivot table on it.


0

This one took a little work! Your question formatting was a little off, so I BELIEVE this is your intended original cell contents: Magazine Production line (Total Downtime :3d 2h 8m 23s) So, based on that, this formula will pull the amount of days and time out and place them in excel date format. =DATE(0,1,MID(A$1,FIND(" :",A$1)+2,FIND("d ",A$1,FIND(" ...


0

Go to Data -> Edit Links (in the Connections group). It will show the linked sources. Unfortunately, Excel has no native way of finding what is linking to the sources (official documentation states: "There is no automatic way to find external references (also called links) that are used in a destination workbook."). It might be a reference in a Cell, which ...


0

Please select the cells which has changed the colors and click on the clear rule. ( You will get Clear rule on conditional formating on home tab.


0

Does this improve the situation?? Function InRange(Range1 As Range, Range2 As Range) As Boolean 'returns True if Range1 is within Range2 Dim InterSectRange As Range InRange = False If Range1 Is Nothing Then MsgBox "Range1 is nothing" Exit Function End If If Range2 Is Nothing Then MsgBox "Range2 is nothing" ...


1

For dates in cells A1:A25 and profit values in cells B1:B25 you can use the following formula: =SUM(B2:B25)/SUMPRODUCT(--(FREQUENCY(A2:A25,A2:A25)>0)) Result as shown: When you have more than 25 rows of data you'll need to adjust the formula ranges appropriately. The formula calculates as follows: (Total profit)/ (number of different dates) EDIT ...


1

Since you only need values found in both columns, you only need to search one column for each value found in the other column. To do this, you could use the following formula in cell C1: =IF(ISERROR(MATCH(B1, A:A, 0)), "", B1) Then copy-paste C1 down until the end of column B. This would display the value of the cell in column B if the same value is ...


0

You can use OnKey to assign a macro to just about any single keystroke. See: https://www.youtube.com/watch?v=MbHYJOvdv8s


-1

write =IF(A1=B1,A1,"") in the cell C1, then drag it down. you'll get the Values in third. non-same username shows blank.


5

You are correct, Excel 2013 has the FORMULATEXT function which shows the formula as text. Since Excel 2010 does not have this feature, there are a couple ways you can show formulas without VBA; By pressing Ctrl + ` (that's the tilde key above the Tab). This one shows all formulas in the sheet. You can place a space it the cell before the =. This causes ...


0

After copying the cells in A12:A16, select B2:B10 (the cells in which to paste). Then go up to the ribbon Home -> Find & Select -> Visible Cells Only. Your selection won't visibly change, but when you paste it will only paste in your desired cells. Source: ...


0

What a gratuitous P.I.T.A ! I was able to make it work by using the "Data Connection Wizard" following these steps.... Select "Other/Advanced" from Data connection Wizard, then hit "next". I thought that localDB as created by sqlserver express would go as "SQL Server". Apparently not! though I can't fathom why. Select "SQL Server Native Client 11.0" ...


0

In all versions of all Office applications since 2003, and possibly earlier, Alt+F11 will open the VBA Editor. There is no need to enable the 'Developer' tab in the Ribbon for Office 2007 and newer for this to work.


2

The full path to the root User folder of the currently logged-in user can be retrieved with: Environ("USERPROFILE") To save the file to the user's desktop, like you've done, you would use: Environ("USERPROFILE") & "\Desktop\" & ... For your specific code as provided ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ ...


1

This is the connection string (SQLServer 2012 / Excel 2013): Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName={mdf file full path} Oh on my PC, I had to use ;Trusted_Connection=Yes, it might not make any sense, since both my laptop and PC are running the EXACT same versions of SQLServer and Excel. Edit I can't reproduce the error ...


0

You don't need a calculated field for this. When you put the Day Count field in the Values area of the pivot table you can change it from summing the value to only including the maximum value. To do this, right-click on cell E2 (in your pivot table) and choose Summarize Values By and select Max


0

Your pivot table is showing the Sum of Max DayCount. Max DayCount is calculated for each record and summed, so this behavior is what should be expected. There are four records for A, and the value of Max DayCount for each A record is 1. The sum of Max DayCount is thus 1 + 1 + 1 + 1 = 4. There are three records for B, and the value of Max DayCount for each ...


0

Your issue could be where you start to apply the conditional formatting. The answer with = AND ($B2 > 0, $F2 = "") assumes you are applying your conditional formatting row 2 of any column. For example if you are applying your conditional formatting rule to an entire column the first row that the rule is applied needs to be the row number in your formula. ...


0

If I understand your question you could use a formula to determine which cells to format. First select the cell you want to apply the formatting, then select conditional formatting on the ribbon, select new rule, and select the "Use a formula to determine which cells to format" type. Then type the following formula =$A$1>$C$1 set the formatting to color the ...


1

Scroll Lock option for the Lenovo L 440 Thinkpad can be enabled by using Fn+K to toggle. Virtual Keypad option as suggested above OSK also works.


2

Building on default locale's excellent answer, (and in response to AHC's request) you could add flexibility by defining some variables and adjusting the formula. Let's start from default locale's result. Unfortunately the formula used here will break if you have more than 2 columns, or if your output doesn't start on the same row as your range. Let's ...


-1

In Excel, DATA tab, in the Get External Data subsection, click "From Text" and import your CSV in the Wizard.


1

Create a boolean variable, set it to false before the loop and change it to true only when the comparison of dates is true. Then, before sending the email check the state of the variable. You can do these changes: 1 - Before the loop, above the line For Each rngCell In .Range(rngStart, rngEnd) put the line ValidDate = False. 2 - After If rngCell.Value - ...


1

As @Prasanna points out, it's a formatting problem. Internally Excel represents date/time values as numbers, where the date is an integer representing the number of days since 1 Jan 1900, and the time is a fraction. When you enter the MAX formula, it doesn't always determine the correct formatting to use. In Excel 2010, use the dropdown box in the Number ...


2

if you are using Excel 2010 and above, then Excel provides you with in internal add-in which allows you to save the file as PDF format. Use the following code in your macro to save your file as PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\46506090\Desktop\Book1.pdf", Quality:=xlQualityStandard, _ ...


0

I found the root cause of this behaviour. The cells in column A are of data type Number, with 0 decimals. I used autofill feature after selecting the first 8 cells, namely, the values 1 1 1 1 2 2 2 2. This caused the later values to be decimals like: 2.54, 2.78, 2.93 etc. These were getting rounded to 3 but since they weren't actually 3; pivot table was ...


0

Sorry not to provide the direct answer you are looking for, but maybe it’s time to switch to a database. It seems like soon you will reach the limitations of a single table (spreadsheet) and could benefit from something more robust. Maybe an access application? I have similar system, but mines tied to a database and some tech I made. Hopefully you’ll ...


1

You should use Conditional Formatting. To set it up, go to Home > Conditional Formatting (this is in the Styles section) > Highlight Cells Rules > Equal To. Then enter the value you wish, and the colour the cell should go. If none of the presets are good enough, you can use the Custom Format option. Just remember when dealing with cell colour you ...


0

This doesn't solve your current problem but I can't paste huge chunks of code into a comment. Here's some VBA that I throw into any workbook I want to be sure to backup. It makes a backup whenever it's called. I usually call it from the Workbook_Open event. If you don't opt for the timestamp, it backs up no more than once a day. For files I'm really nervous ...


0

I had the same issue and this is what I found. The issue happens only when inserting new rows moving to the end of the last row and pressing TAB. If I add the new row using the menu option "Insert -> Table Row Below" the format is inherited properly to the new row. Looks like a bug in Excel when adding rows using TAB. Hope it helps.


0

I'm not so good in VBA but this code can do that: Option Explicit Public Sub AlignCustNbr() Dim ws As Worksheet Dim i As Long Application.ScreenUpdating = False Set ws = ActiveSheet For i = 2 To ws.Columns.Count If (Trim(ws.Cells(1, i).Value & "") = "") Then Exit For End If ' Call ...


1

As mentioned elsewhere, you should be using commas, not semicolons. In addition, because you only have two possible return values, I would recommend using the OR function instead of nesting your IF statements: =IF(OR(E3=E2, F3=E2, E3=F2, F3=F2), "No", "Yes")


0

You need to use commas instead of semicolons in your formula: =IF(E3=E2,"No",IF(F3=E2,"No",IF(E3=F2,"No",IF(F3=F2,"No","Yes")))) If you are going to have a lot of these yukky nested if clauses, it might still be worth considering a little bit of VBA to introduce a SWITCH function - see this example, which can then be used as a formula and is much easier ...


-1

What worked for me in Office 2013 was: =IF(A2=""," ",A2) Where on the first set of quotations there is no space, and on the second set there is a space. Hope this helps


0

Here's a way to do it with no scrolling or dragging required. Select the cell with your formula. Click in the Name Box (to the left of the formula bar, highlighted in the image below) and type the address of the last cell you want to fill down to. In my example, I want to fill the formula from A1 to A500000, so I type A500000. Press Shift+Enter. This ...


0

This is a faster way than dragging down: Apply this formula to the first row and the last row Copy this formula from the first row Press Ctrl + Shift + Down Arrow to select all rows between 1st and last rows in this column Paste with Ctrl + V Edit: To move quickly to the first or last row, first go to a column that already have all rows filled with ...


2

If you are adding a formula to a blank column and you want it filled down as far as the data in another column you can use the following technique: Move the cursor to the top of the blank column. Put in your header cell if needed, and the formula below that, or at the top of the column. Ctrl+Down Arrow, this will take the cursor to the bottom of the empty ...


0

Not sure I understand your question but named ranges might be what you need. You can give a single cell or group of cells a specific name and refer to that name in formulas instead of using the cell address. In Excel 2007 it's in the formulas tab, hopefully it's still there in 2010.


0

If Excel stubbornly refuses to recognize your column as date, replace it by another : Add a new column to the right of the old column Right-click the new column and select Format Set the format to date Highlight the entire old column and copy it Highlight the top cell of the new column and select Paste Special, and only paste values You can now remove the ...


1

I would suspect the issue is Excel not being able to understand the format... Although you select the Date format, it remains as Text. You can test this easily: When you try to update the dates format, select the column and right click on it and choose format cells (as you already do) but choose the option 2001-03-14 (near bottom of the list). Then review ...


0

I think you can do this nice filter in Excel 2010 too (while I'm using 2013). UPDATE This code will do it: Sub hide_pivot_items() Dim pt As PivotTable Set pt = ActiveSheet.PivotTables("PivotTable2") Dim cell As Range For Each cell In pt.PivotFields("Data").PivotItems("S").DataRange If cell.Value = vbNullString Then 'hiding row ...


0

It's funny you should mention this because I had come across that program while researching other options! So how would I do an iframe kind of insert along the lines of what I had described in Expression? As I said, I am used to SiteBuilder where I can just drop an iframe box, paste my code, size the area, and I'm good. Thank you!


3

To plot all the teams (it's a little cluttered, but I bet if there was more order in the data it would look better) you can arrange the data as shown below. Make a line chart (top chart), add and format the up-down bars (middle chart), then format the Target and Actual series to use no lines, and the T1 and T2 series to use the same format. I used two series ...


0

You don't need VBA at all. You just need a better understanding of the flexibility behind conditional formats. Because of that, I'm going to answer the question even though it's a poor one. You can sort by column D as is and the totals row will be at the bottom if it's the only one with a blank in it. Apply your conditional formats to the entire column as ...


0

You need to set a custom cell format. Select the cell format dropdown and change it to select the number of digits required. You can remove the comma separators as you wish


0

I have had this issue with a few clients. Both were using a mapped drive to access the file. One user would open the file, it would change the links to %appdata%\XLSTART*corresponding file path to mapped drive* I addressed this by making Excel open in the mapped drive for these users. Options, Advanced, General, At Startup, open all files in: (put the ...


0

You can but it's messy and dangerous. Allow VBA to read / write VBA (File > Options > Trust Center > Trust Center Settings > Macro Settings > Trust access to the VBA project object model) Create a personal macro workbook (if you don't already have one) In the ThisWorkbook object for your personal macro workbook, use the Workbook_Open event to map Ctrl+S to ...



Top 50 recent answers are included