Tag Info

New answers tagged

1

It looks like on import of the text file, Excel removed the comma separator from your numbers greater than one thousand. So to Excel those look like the text representation of what you see as a number so 10 000.00 Excel sees as the characters "10" + " " + "000.00" Use the substitute function =NUMBERVALUE(SUBSTITUTE(L1," ","")) to remove the spaces and ...


0

It is the format selected by Excel when importing the data. More specifically, it is most likely the accounting format. To fix, select a different format from the home tab or right click on the cell and select properties. Select custom format and change to what you want.


0

{=IF(AND(ISNUMBER(A1:C1)),SUM(A1:C1),"")} It is an array formula and must be entered using by pressing Shift + Ctrl + Enter at the same time. If you get it right you will see the { and } brackets around the formula automatically. How it works ISNUMBER ensures the cells only have numbers stored inside. AND ensure that all the cells have numbers IF ...


-2

Answered by @XOR LX in question comments. Posting this to mark question resolved, but XOR LX deserves the credit.


3

This array formula works for me - =IF(AND(A1:A3>TODAY(),B1:B3="Yes"), "Yes", "No") Type it and hit ctrl+shft+enter - you will see curly brackets appear.


0

This is a simplified version of an answer... Put this in C2, assuming your table above begins in A1: =IFERROR(FIND(UPPER(LEFT(A2;FIND(" ";A2;7)));UPPER(B2));0) Copy down into the remaining cells. It takes the straight forward step to search for a second space at or after the 7:th position in the A column. This is of course as a measure to avoid having to ...


0

The formula is: =IF(COUNT(C5:D5)=0,"",IF(ISNUMBER(C5),E4-C5,E4+D5)) It does only the subtraction even if both C5 and D5 contains numbers, as you wrote in your question.


2

What your customer is trying to tell you is that however you're generating dates is ending up with a character string instead of a date value that Excel understands. If the text format of your date column is because you are typing periods into Excel when entering dates: STOP. Enter dates with slashes and Excel will recognize it as a date: Just hit Enter. ...


0

Had similar problem, tried formatting the data as date but to no avail. Then I realized there was a leading space in the date, edited the field and removed the space - hey presto everything ok. Not great for large amounts of data, but thinking about it further I could have maybe checked it and edited the data in Wordpad or a similar editor.


1

This method is overkill in this case, but you could also do this with an array formula: =AVERAGE((A2:A7)-(A1:A6)) To enter an array formula, type it in, then, with your cursor still in the formula editing field, press the combinationCtrl+Shift+Enter. The formula should now should show up as ={AVERAGE((A2:A7)-(A1:A6))} ...but you can't type those ...


1

You're overthinking this:   (131-130) + (135-131) + (140-135) + ... = 131 - 130 + 135 - 131 + 140 - 135 + ... = -130 + 131 - 131 + 135 - 135 + 140 ... = -130 + (131-131) + (135-135) + ... = -130 + 0 + 0 + ... + 120 = 120-130 i.e., the sum of the differences between pairs of consecutive numbers is simply the difference between the last number ...


0

It's been a long time since my last Statistics course, but it sounds like you're looking for the average deviation, correct? Try =AVEDEV() Another option would be to go ahead and use another column and hide it.


2

The problem you're having is that a calculation in Excel is meant to be continuously calculated but you're wanting to treat it as a one-time calculator. In this case, I would probably suggest just adding another column or use another cell somewhere else on the sheet to calculate your final result.


1

This method worked on my computer. Close all office programs. Click windows button + R, then write regedit and enter. First backup this key HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel then delete it. Reopen excel.


0

Close all office programs. Press Win + R Type regedit and press Enter. First backup this key HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel Then delete it. Reopen excel.


0

It is pretty easy to accomplish this without using VBA. Copy the Delivery order column from Sheet 1 to Sheet 3 column A In Sheet 3, Column B use the following formula vlookup(A2,![sheet1]A2:A10,2,0). This will bring the Quantity from Sheet 1 to Sheet 3 Column B In Sheet 3, Column C use the following formula vlookup(A2,![sheet2]A2:A10,2,0). This will ...


1

I know this is old, but as a reference: Option Explicit Private Const Q As String = "'" Private Const ROOT As String = "E:\Test\" Private Const FLDR As String = "SubFolder" Private Const DASHBRD As String = "Db.xlsx" Public Sub updateAllFiles() Dim ws As Worksheet, cn As ADODB.Connection, rs As ADODB.Recordset, sql As String ...


1

I know of no way to do it directly without VBA. But if you select all sheets that need to be changed then changing it on one sheet will propagate the change to all sheets. (I don't select multiple sheets very often, but it's either Ctrl+click or Shift+click on the tabs. I think Ctrl+click selects individual sheets and Shift+click selects all sheets from the ...


1

Actually I realised this is very easy to do with the LOOKUP() function. In Sheet 2 Cell B1 I would put: =LOOKUP(A1,'Sheet 1'!D1:F1,'Sheet 1'!A1:C1)


0

There is a second way to do this. It uses Windows Script Hosts Exex method. It has the great advantage to read back values from your external PowerShell, command line or whatever other command line tool you have. So you have a two-way communication between Excel and Powershell Excel macro Sub RunAndGetCmd() strCommand = "Powershell -File ...


0

The problem is likely a phantom EXCEL.EXE process. Close out all versions of Excel, then go to Task Manager. In the 'Processes' tab, look for an EXCEL.EXE process. If you find one, end it. My problem was similar. I had two instances of Excel, a good one and a bad one. When I double-clicked on any Excel file, the file would open in a bad instance of Excel ...


0

Select all the column and go to Locate and Replace and just replace "/" with /.


1

If you can't set a min/max then my suggestion would be to use a dummy series. The points could be set using ceiling/floor functions on your data set for the second one you would have a dummy series something like x y 201 491 204 492 Give the series the name "", and format the dummy series so there are no points and no lines. No one will see the ...


1

This is an interesting question, and I can see your question is actually 2 parts. Why does the macro choose Split instead of Pane and How to work around it. I don't know the answer to 1, so I'll focus on 2 only. Since you want to unfreeze you need to use freeze! Sub DoThis() Columns("E:E").Select ActiveWindow.FreezePanes = True End Sub


0

I would create a range in a third column based on start and end dates, then count the number of occurrences of the Search value with COUNTIF.


0

B2: Name: Fred Flintstone Old Location: Bedrock AK Old Server Name: \\Server\Folder$ Old Org Unit: BED Old Profile Path: \\Server\Folder$\Folder Old Home Directory: \\Server\Folder$ New Location: RockVegas NV New Server Name: \\Server\Folder$ New Org Unit: RVS and B3 formula: =RIGHT(B2;LEN(B2)-FIND("New Server Name:";B2)-15) and B3 displays: ...


0

I'm afraid you have to use the "Text" cell format and use a formula that will output a text value with the required separators =LEFT(A1;2) & ":" & MID(A1;4;2) & ":" & MID(A1;7;2) & "." & RIGHT(A1;3) Excel doesn't handle milliseconds in named formats...


1

Click the Developer ribbon > Document Panel and untick "Always show Document Information panel on document open and initial save". (If you don't see the Developer ribbon, you need to enable it first via Right-Click on any ribbon > Customize the Ribbon > tick "Developer" in the list of ribbons on the right.)


2

I should uncheck the "formula" box in Tools > Options > View but there is no "Tools" menu In Excel 2010 you can press ctrl+` (grave accent) to toggle the display of formulas. Display or hide formulas You can control the display of formulas in the following ways: Switch between displaying formulas and formula results on a worksheet ...


0

Your code looks good with exception of the line that adds the hyperlink. Here you have your variables surrounded by double-quotes which is causing them to be interpreted as literal values instead of variables. Try this instead: ActiveSheet.Hyperlinks.Add Range(Active.Cell), path & “\“ & sheet & “\“ & cell


0

One way to avoid the problem: Use INDIRECT() on textually referenced File and Sheet names. Example: Make Z1 be 'C:\..\[B.xls]!Sheet1' (with the quotes? Untested) Then use =INDIRECT(Z1&"!A1") where you need to reference cell A1 in that sheet.


0

I created a macro for this same situation that removes all the custom views on open and then removes extra users that are not using the sheet to prevent corruption and file bloat. The code for the macro is at the link below. How do you prevent corruption of shared Excel files?


0

The solution can be to open the file where you have given the links and then go to Data > Edit Links > Open Source. This will open the source without making the #ref error. Another alternative is to keep two versions with and without the links. The version without links can be made by Data > Edit Links > Break Link. I think it is some kind of a ...


0

The Grand Total on a pivot table is fixed to using the same calculation as the Calculated Field. So there isn't a way to modify it. A solution is to perform the commission calculation in the data table. You can then use the pivot table to summarize it. This results in the Grand Total being correct.


0

Take a look at my answer here I was in a similar situation and it is most likely caused from excel thinking users are logged in when they are not and tons of custom views being saved in the file my script clears all that out. How do you prevent corruption of shared Excel files?


2

If you're open to VBA, there's the Application.AutoCorrect object that has .AddReplacement method KB. You could do something like this - Sub test() Dim rng As Range Set rng = Range("A1:A100") With Application.AutoCorrect For Each c In rng .AddReplacement c, c.Offset(, 1) Next End With End Sub That's assuming you list is in A1:B100 where ...


0

Its 'Fn' + 'S', accidentally pressed instead of 'Ctrl' + 'S', which is the toggle shortcut on my Dell - and most Dells, in general. Try if it works for you.


2

You will need to add them to the .acl file as explained in this KB. To use your AutoCorrect entries on another computer (or to share them with another user profile on your computer), locate the .acl files in the %AppData%\Microsoft\Office folder. (You can type the path in Windows Explorer exactly as shown here and the %AppData% environment ...


0

Option Explicit Public Sub COPYSelectedSheetsToCSV() Const FLDR As String = "/Users/reginaho/Desktop/Upload/" Const ASK_ As Boolean = True Dim ws As Worksheet Application.DisplayAlerts = ASK_ For Each ws In Worksheets If InStr(LCase(ws.Name), "upload") > 0 Then ws.SaveAs FLDR & ws.Name, xlCSV Next ...


3

Maybe what you want to do is simply data filtering. This functionality is available in main spreadsheet programs and I will explain how to use it on an example. Plants are listed in a table: For each plant, it is written its type (fruit, vegetable...) in column "type" and its color in column "color". Select the header items and apply "auto-filter" on ...


0

I believe there are many ways to do. I do similar things every month and I will do it in this way: In my originally data, no column was there to store information of the datetime. (My data source doesn't provide this column.) The crucial step is to manage your data before further processing. What is missing from the data is the datetime information of ...


0

Try the following versions: both should fix the initial problem ("Run time error 424") Version 1: your initial code, in a more manageable format Option Explicit Sub copyColumns1() Const FROW4 As Long = 25 Const FCOL As Long = 1 Const LCOL As Long = 9 Dim ws4 As Worksheet, ws5 As Worksheet Dim lRow4 As Long, lRow5 As Long, i As ...


2

Here’s a tricky way (that might be considered elegant).  I’ll assume that < 83 is the “good” condition.  Use this formula: =CHOOSE((D1<83)+(F1<83)+1, "bad", "neutral", "good") Logical expressions like D1<83 evaluate to one of the Boolean values TRUE and FALSE, which are numerically equal to 1 and 0, respectively.  By adding ...


2

Well, I made a quick attempt and this and came up with a rough version that works. It probably isn't the best so hopefully someone responds with a more elegant solution, but try this: =IF(AND($B$2<=$B$1,$B$3<=$B$1),"Good",IF(AND($B$3>$B$1,$B$2>$B$1),"Bad", "Neutral")) B1 = 0.83 (just my value for budget) B2 = YTD value entered by user B3 = MTD value ...


1

You may have to do it manually, but there is a feature to assist you: Trace precedents (in Formulas tab in Excel 2013). This will add arrows showing where the formula you have selected originates, so if you selected the total sales in column J in your example and clicked trace precedents then it would draw arrows from F9 and L9 to it.


0

With the start time in A1 and the end time in B1 consider: =IF(B1>A1,B1-A1,1+B1-A1)*24*60


0

You want to use =IFERROR(DATEVALUE(B1-A1),MOD(B1-A1,1)) EG (and I've used different times to you) A B C 1 23:50 00:32 =IFERROR(DATEVALUE(B1-A1),MOD(B1-A1,1)) 2 21:53 22:05 =IFERROR(DATEVALUE(B2-A2),MOD(B2-A2,1)) The result of C1 would be 00:42, and C2 would be 00:12


0

An easy way to do this is with a helper column that keeps track of the group count. I'll assume your example is in columns A:F and will use column G for the helper. I'll assume your column labels are in row 1 and the data starts in row 2. Cell G2 would contain: =IF(AND(E2=E1,G1<6),G1+1,1) Copy that down as many rows as you need. This compares each ...


1

For first name =LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256), FIND("_", MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256))-1) for Last name =MID(CELL("filename",A1),FIND("_",CELL("filename",A1))+1,256) Not sure what you mean by "it updates all the sheets" - these are formulas that you should paste in the cells on each ...


1

You can do it easily with VBA: Sub namestocells() Dim wkb As Workbook Dim wks As Worksheet Set wkb = ThisWorkbook totalwks = wkb.Worksheets.Count For i = 1 To totalwks Set wks = wkb.Sheets(i) sheetname = wks.Name splitname = Split(sheetname, "_") mn = UBound(splitname) If mn = 1 Then ...



Top 50 recent answers are included