Tag Info

New answers tagged

0

right click on the cell -> Format Cells -> Choose Category Date -> Choose from Local Settings English (UK) -> select format yyyy-mm-dd That's without changing the localization settings of the whole OS as above mentioned. Cheers


0

You don't need any code for this. Just select the cells in the header when you are customizing your sort. And the sorting criteria will be saved. Tested OK on Excel 2010. Reference: "If a cell in the header row is selected the 'sort by' criteria is saved."


0

There are a couple of syntax errors in your formula. One of them is the "" used as your false return value is terminating your n.formula string early. When you want to use a double quote (") inside a string that delimited by double quotes, you need to escape the internal quotes by doubling them up. Ex: myString = "This is a "string"." - Would fail, or at ...


0

You must double-up on the double quotes: n.formula = "=IF(AND(ISNUMBER(K39),ISNUMBER(L39))=TRUE,IF(ISNUMBER(M39),(K39-L39)*M39,(K39-L39)),"""")"


0

its not clear if your roll number and value are in one cell, assuming they are in B28 you could use:- ="Roll-No:- "&VALUE(RIGHT(B14,5))+1 and copy the block


0

I'm not sure this is the correct way to solve it, but this works on my machine with the dutch language. In dutch the y (for year) is substituted for the j (for jaar). TEXT(G1533, "mm-dd-jjjj") Or ... you could resort to this: CONCATENATE(TEXT(G1533, "mm-dd-"), YEAR(G1533)) Also see Excel TEXT function does not work properly


1

If you don't want to deal with an array, you could do something like this (assume the table starts at E5) Sub test() Dim x As Integer Dim y As Integer Dim item As String For Each c In Range("A1:A3") x = c.Value y = c.Offset(, 1).Value item = c.Offset(, 2).Value Cells(4 + x, 4 + y) = item Next End Sub To find the empty cells and put in ...


0

Based on the wording of your question, what might be complicating the problem for you is thinking of the adjustments you want to add as additional "conditions" (if a cabinet dimension varies from the standard, then adjust). Actually, they aren't conditions, just calculations that can be incorporated into your formula. For standard dimensions, the ...


2

A first crack would be =VAR(IF(DJ4:EH4=-99, "", DJ4:EH4)) Press Ctrl+Shift+Enter after typing this, to make it an array formula. If your data contain any blanks, the IF(…,"", …) causes them to be processed as zeroes, so revise that to =VAR(IF((DJ4:EH4=-99)+(DJ4:EH4=""), "", DJ4:EH4)) using + to combine two Booleans in the sense of “or” (because OR(…) ...


1

You are close. Enter the following formula, but instead of pressing only Enter, press Ctrl + Shift + Enter as this is an array formula. =VAR(IF(DJ4:EH4<>-99,DJ4:EH4))


0

I've had the same problem, and tried each solution provided, with varying success: 1) I opened the Names Manager and, sure enough, there were a bunch of Names I never created; however, they were NOT the names I was being warned about. I deleted them anyway to get rid of the clutter, but that wasn't the solution. 2) I tried the code provided by Viquar ...


0

I think it's just that the show formulas is checked in the auditing pane, just press crtl+` and it should revert back.


0

Select all by pressing Ctrl+A. Then press Alt+H, O, I.


2

This is pretty broad, but essentially I think I understand what you're trying to accomplish. Let's take an example spreadsheet like this - Title Tasks Percent Done ch1 task1 75% ch1 task2 50% ch2 task3 25% ch2 task4 75% ch3 task5 50% ch3 task6 25% From what I gather, you want a summary of ...


0

This sounds like a job for a PivotTable but you're going to need row headers. First, setup a dynamic named range that will capture your list in A:C and grow along with it. To do this, click on the Name Manager in the Formulas ribbon. Click New, give it some name (I called mine rngPivotData) and use this for its "Refers to:" formula: ...


-1

in C1 write =if(A1=B1 , 0, 1). Then in Conditional formatting, select Data bars or Color scales. It's the easiest way.


1

I have tested it. It actually works works well. Several points to have in mind before using the current version of code: It should be added to Word VBA, Not Excel or other (this might be the reason why you got the "object equired" error). It processes just .docx It processes all actual MS Word tables, not pictures that might look like tables. I have ...


1

And if you really want a macro, use this (taken mostly from here, with some improvements): Option Explicit Sub AutoSum() Const SourceRange = "C:F" Dim NumRange As Range, formulaCell As Range Dim SumAddr As String Dim c As Long For Each NumRange In Columns(SourceRange).SpecialCells(xlConstants, xlNumbers).Areas SumAddr = ...


0

Instead of a macro, it seems to me that what you ask is already part of Excel as a feature, called AutoSum. Just place the cursor under a contiguous set of numbers (a region, in Excel-speak), and press the AutoSum button. Excel will come up with the formula for you. The AutoSum button is on the ribbon, on the "Home" tab, far to the right, and has an icon ...


0

I was having a similar problem. The reason the formulas were pasted with the link to WBA was that the tab (sheet) I was working on in WBA was named differently from that in WBB. For me, it was always "the last sheet" but one was named 'MinFlow' and the other was named 'NormalFlow'. I renamed both to 'Results' and the copy/paste worked as I wanted it to - a ...


1

Yes you can, with the IF statement. In B1 put =IF(A1=24,"x","") Or, if you want code mapping, so that it works for all numbers to letters, you can do: =CHAR(A1+96) This works because the letter 'x' is at position 96+24 in the ASCII character set (this working is depending on the character set you use, but will generally work). If you want upper case ...


-1

Click anywhere in the table. Tip: This displays the Table Tools, adding the Design tab. On the Design tab, in the Tools group, click Convert to Range. Excel Ribbon Image Note: Table features are no longer available after you convert the table back to a range. For example, the row headers no longer include the sort and filter arrows, and structured ...


0

We can get tricksy with this. I pasted your data into the range A1:F5. Below that, I created a table whose end result looks like this CSV: Door,Apts,44,47,48,50 2'0,50 (1),,,,50 (1), 2'6,44 (1), 47 (1), 48 (1),44 (1), ,47 (1), ,48 (1), , 3'0,48 (1),,,48 (1), , 4'0,44 (1), 48 (1), 50 (1),44 (1), ,,48 (1), ,50 (1), 5'0,44 (1), 47 (2), 48 (1), 50 (1),44 (1), ...


0

Try using OFFSET() as in =IF(B2:B5>0;A2:A5 &", "& OFFSET(A2:A5;0;1);)


0

Click the Microsoft office Button and select Excel Options. Choose the Formulas category and in the "Working with formulas Section", select the "Use table names in formulas" option.


4

The key is to include the date. Without the date, Excel has no basis to know that 23:17:47 was from the previous day. I mocked up a couple of values to illustrate. The date/time values are in column A and the Y values in column B. I selected a scatter plot, which treats the X axis as a continuous variable. The rest is formatting. The X axis shows ...


0

This small Event macro will place the latest A1 value at the end of the first row: Private Sub Worksheet_Calculate() Dim v As Variant, N As Long v = Range("A1").Value Application.EnableEvents = False If Range("B1").Value = "" Then Range("B1").Value = v Application.EnableEvents = True Exit Sub End If N = ...


0

Assign 'Fill Series' to a position on the quick access toolbar. Let's say position 2 for this example. Highlight the cell you want to fill down and the empty cells you want to fill - can be done using Shift+Arrow. Now Alt+2(assigned QA position) then Enter. Note - Highlight 2 cells plus the targets if you want Excel to infer the numeric series to fill. ...


0

To expound on the answer from Tiago Cardoso, I added a condition to check to make sure the maximum row height is not exceeded when increasing the row height with the resize factor variable. Sub FixRowHeight() Dim oRange As Excel.Range Dim oRow As Excel.Range Dim dblFactor As Double Set oRange = Sheets(1).Range("A1:O367") dblFactor = 12 ...


0

This is pretty simple, use a for loop with an if condition like this - Sub test() For Each c In Range("A:A") If c <> 0 And c.Offset(, 1) <> 0 Then c.Value = 1 End If Next End Sub You can change the range to fit your data, or you can try to get the usedrange. I don't quite understand your second part.


0

You could use Excel's Find-and-replace feature to do this. Just select the column with the formulas and press Ctrl+H to open the Replace dialog. Search for: Sheet4!$A$1:$H$50,7,0) Replace with: Sheet4!$A$1:$H$50,5,0) Just be careful that you don't have any formulas in the selection that include the search string but you don't want to change.


1

I had a similar problem. I had to define a custom Style and apply the Style to the cells. This resolved the problem for me.


3

You are using the function properly. Excel is using error checking to warn you there may be cells which need to be included. Excel Help states: Formulas which omit cells in a region - A formula might not automatically include references to data that you insert between the original range of data and the cell that contains the formula. This rule ...


0

If your data starts in A2 - In B2 use - =LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-1) to get customer in C2 use - =REPLACE(A2,1,LEN(B2),"") to get full address


0

Try this... In this example I assume you paste your data into cell A2, so you have row 1 for headers. When you split by columns the data fills columns A to M; with N being the first blank column; adjust column references as appropriate for your data. Use Data, Text to Columns to split by delimiter, selecting space (normal left to right) In the column ...


0

These files are saved under your user profile's temporary file folder. Excel also cleans these files up, so it's likely you data is deleted if you haven't saved it or Excel has been closed since then.


0

I had the same issue, so everyone's contribution led me to the way the works for me (so far). ADD a blank row below your Multi-row(s) Header (if your header is 5 rows of info and/or odd cell sizes, then your NEW blank row will be Row6). Click on Row6 (to the far left/ actually click the "6" (Excel Row #) to select the entire row / to infinity). BE sure NOT ...



Top 50 recent answers are included