Tag Info

New answers tagged

0

You want to disappear start and ending " and others to remain? Option 1 Use text editor with RegExp to text file (for example: Notepad++). Find and replace (sect option to allow RegExp) Find: ^\"(.+)"\t"(.+)\"$ Replace: $1\t$2 Now you can import without text qualifier. Option 2 Import without text qualifier selected and use MS Office RegExp (in ...


2

Put these two subs in a standard module and the run MakeButton Sub MakeButton() ActiveSheet.Shapes.AddShape(msoShapeOval, 228#, 557.25, 126.1, 126.1).Select With Selection .Characters.Text = "1 ADDER" .ShapeRange.Fill.Visible = msoTrue .ShapeRange.Fill.ForeColor.SchemeColor = 3 .ShapeRange.Line.Visible = msoTrue ...


1

I have two solutions for you: Convert your data to a table (insert - table) and you won't need to worry about formula auto fill when you add new data, so your users range will be really the range you use. If the previous solution doesn't work: select a cell in a data column (not one with pre-filled formulas that extend forever) and press CTRL+ DOWN ...


1

User [daverunt] on Mr. Excel Forums gave this answer. It may not be applicable in all situations, because you have to save it to a ".txt" file, but it was the quickest way I could get around the problem when I had the same issue as in the original post. Mainly, in one step, it will prevent the HTML-interpretation of all your lines of text. I think the ...


-1

I don't know the reason yours isn't working as it should, but here's an idea that will hopefully get rid of the behavior. (It's a little bit risky in the sense that you might conceivably lose some work in the event of a crash.) Options -> Save -> uncheck "Keep the last autosaved version if I close without saving"


0

Here are the instructions for Excel 2003: Go to the Tools menu and select Options. Select the View tab. In the Window Options section, uncheck the box marked Page Breaks. Click OK.


1

If you already have cells in the column to the immediate left or right of where you want to fill your pattern, you can select your pattern and then double click the handle at the bottom right of the selection. Excel will autofill your pattern down as far as you have continuously filled cells to the left or right. For example, double-clicking the handle in ...


0

I think most elegant way to do this with a pivot table: Set your name column as column label, set your number column as values and select to summarise it by minimum.


0

If I understand well you can select your range, your question is just how to distribute the formula. Here are two easy ways I use frequently. - if your formula is already entered in the first row: Select your range containing also the formula and press 'CTRL+D'. - If you haven't yet entered your formula: Select your whole range and enter your formula ...


0

=OR(COUNTIF($A$2:$A$18,A2)=1,SUMPRODUCT(1*(B2>$B$2:$B$18),1*(A2=$A$2:$A$18))=1) You need to change the direction. It needs to be one position higher than the lowest not one position lower than the highest. Rereading the post led me to believe OP looks for the lowest value. In this case you're looking for the value 0 position from the lowest (...))=0)), ...


1

You can use this VBA to do this. You will have to change what your source cells and destination cells will be. Sub Auto_Fill() Range("A1:A5").AutoFill Destination:=Range("A1:A100000"), Type:=xlFillDefault End Sub


2

I think you can do this by copying the cells that contain the pattern. Then click in the address bar and enter the range you want to fill down. Not tried it but I believe it should work. UPDATE: Select the first cell of the series, click in the address bar and extend the single cell to a range. E.g. start with cell b2, edit the address to b2:b100000. Press ...


0

Start entering the text with a single quote ('): '4-2


1

The easiest way is to prepend an apostrophe/single quote (') to the string you're entering. So, in your example, type: '4-2


0

Alt + A, R, R or A in sequence will refresh the data.


1

I'd do it with pivot chart: put KRA to "axis fields" and total rating to values (use max function). To facilitate updating of your chart convert your data range to data table first so you won't need to change source data range when you enter data. Also you can add very simple code to your sheets module to update chart when data changes. (just change ...


1

This problem seems well suited to Excel.  But first, let me question your question.  Suppose your data look like this: 60 120 180 45 90 135 35 70 105 60 You mentioned selecting the largest three values with LARGE().  The three largest values in the above are 120, 180, and 135.  Is that what you want, or do ...


0

You could use an array formula that will work with unsorted and repeat dates. The formula in F2 is =LOOKUP(2,1/(($B$2:$B$6=E2)*$A$2:$A$6=MAX(IF($B$2:$B$6=E2,$A$2:$A$6,0)))),$C$2:$C$6) This is an array formula and must be confirmed with Ctrl-Shift-Enter. Then you can copy it down. Note that in the screenshot the latest date for event A is in the first ...


0

Actually, I'd do this in the script where you capture the data. In addition to capturing the size in one "data" file, save the current size, on next run compare the new current with the previous. If the new is smaller than the old, save TRUE against the file name in a separate CSV file, otherwise save FALSE. Make sure both data and this file are indexed by ...


0

The question is a little vague. You state that you want the "latest date" in the count column? If you only want the latest date, Pivot Tables are the way to go or alternatively, possibly the Microsoft PowerQuery addin. I assume you really want the count to show the total number of records for the campaign but to only show the latest campaign date? Again, ...


1

Give this a try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False Target.Offset(0, 3).Value = Target.Value Target.Clear Application.EnableEvents = True End Sub


0

if you don't need to turn off fill handle, you have to take care when the cursor turns on black cross, this means you are going to move to the end of list. move back until the cross is white again and only editing will work


0

If my understanding of the problem is correct and you are looking for a singular solution to match each batch payment it is possible to work out using the solver add-in which is available through file->options->Add-Ins->Solver-Addin. The details on how to use this add-in are outlined very well in this previous post: Excel: Find a subset of numbers that add ...


1

Not sure if this is really what you are looking for. But it might give you a good starting point. Using the IF function you can pull the data from one page to another, based on a condition. This is assuming that there are three cells per row (Tech Name, test y/n, and test name). On Sheet2 place this in cell A1 =IF(Sheet1!B1="N",Sheet1!A1,"") And place ...


1

First install the following event macro in the worksheet code area: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim A As Range Set A = Range("A:A") If Intersect(Target, A) Is Nothing Then Exit Sub Cancel = True Cells(Target.Row, 3).Interior.ColorIndex = 27 End Sub If you double-click on any cell in ...


0

This VBA code should work: Private Sub Worksheet_Change(ByVal Target As Range) Dim wks As Worksheet Set wks = ActiveSheet theRow = Target.Row theColumn = Target.Column theValue = Target.Value If theRow = 1 Then If theColumn = 1 Then Application.EnableEvents = False wks.Cells(1, 4) = theValue Target.Clear ...


2

You need to force Excel to treat the contents of the cell as a text value instead of a number, which it does automatically with blank values. =A2 & "" This will force Excel to making that cell reference a text value, thus preventing the conversion of blanks into zeroes.


0

IF your reference data is only either a numeric type (non-text) or empty, and you may have 0's, then this is my preferred approach, with only entering formula once. Admittedly, a slightly indirect way, but it is best I think because: you don't need extra cells to put the formula in and then reference the second cells you don't need to type the formula ...


2

It's because it's using the default formatting and detecting a time. Before entering the data, select the cells then change the formatting to text (right click the cells and choose "Format...").


1

Excel is changing what you type "1 A" to "1:00 AM" because the programmers believe that most people would want the time if they put "1 A" into a cell. So entries between 1 and 12 with an A or P gets converted into a time. To stop this, you can either disable auto-correct (I will need to look up the commands), or you can format the cell as text before you ...


0

I don't think there's any solution so long as the header is merged. I propose a workaround: Un-merge the header cells, format things so it still appears the same although it's now two cells, and use VBA so the user can't select the first row. For instance, if the cells A1:A2 were the header, the VBA to ensure that A1 can't be the active cell could be this: ...


0

=IF(OFFSET('Sheet 1'!$A$1,MATCH('Sheet 2'!A1,'Sheet 1'!$A:$A,0)-1,1)<>"",OFFSET('Sheet 1'!$A$1,MATCH('Sheet 2'!A1,'Sheet 1'!$A:$A,0)-1,1),OFFSET('Sheet 1'!$A$1,MATCH('Sheet 2'!A1,'Sheet 1'!$A:$A,0)-1,1)) What you requested for the unfound values is the same you requested for found values, if you really want to fiddle around with shifting based on ...


0

Assuming that your columns are A, B, C, …, and that your data start in Row 2, put =IF(C2=C3, A3, INDEX(A$2:A$20, MATCH(C2, C$2:C$20,0))) (replacing 20 with the number of the last row that has data, or a larger number) into cell B2 and drag down.  This says, If the value in Column C on this row (C2) equals the value in Column C on the next row (C3), then ...


1

There is no way to move the icon set. However, you can achieve a visual effect that will help eliminate confusion with multiple columns displaying icons. Highlight the column which you wish to bring the numbers and icon closer together. Right Click. Select Format Cells. Click the Alignment tab if it does not appear by default. Locate the Horizontal Text ...


0

Select cells you want to move. Now try to move them by drag n drop to different worksheet (different tab). I know, it scrolls. Here is the tricky part: just press cmd (mac) or alt (win) and it will let you drop the cells to another tab.


0

I was interested in something similar and was able to achieve it. Though I didn't use conditional formatting, I think this example will show you how to achieve what you want. Put the numbers 1,2,3,2,1 in cells b2 through f2. Put the formula =REPT("|",B2) in cell b4 and copy through f4. Change the text direction to go up. That's achieves the outcome but ...


1

Steven, here is one possibility if you don't want to go the macro route. Have your master list of titles as a named range with the unique number next to it (e.g., A. Approaches to Sustainable Agriculture, B. Economic Sanctions in U.S. Foreign Policy, A1. Trials of the Century). Make sure you have enabled autocomplete in cell (in backstage view, Options, ...


0

If I understand your question correctly, you want the cell under "LatestAVG" on Sheet1 to show an average of all of the value in the "SemersterAvg" column. If that is the case, insert the following formula under "LatestAVG" in Sheet1: =AVERAGE(Sheet2!D:D) This formula assumes that the tables above begin in cell A1 of both sheets.


0

You need to use Pivot Table, it's really easy to do exactly what you want with just a few clicks once you learn how to use that feature. To create a PivotTable or PivotChart report, you must connect to a data source and enter the report's location. Select a cell in a range of cells, or put the insertion point inside of a Microsoft Office Excel table. ...


0

Add a validation rule. If the entered value is not in Time/Date format (which usually means it is String) then your formula will default. I agree with @jcbermu, you most probably have issues with regional rules and this is the way to detect whether or not you use valid dates.


0

Check these links https://msdn.microsoft.com/en-us/library/aa242706%28v=vs.60%29.aspx https://msdn.microsoft.com/en-us/library/office/ff840672.aspx I think this will help you.


0

You actually don't need the helper sheet to achieve this, try this formula: ="HRM: "&text(today(),"dddd")&" the "&text(today(),"d")&MID("thstndrdth",MIN(9,2*MOD(day(TODAY()),10)*(MOD(day(TODAY())-11,100)>2)+1),2)&" of "&text(today(),"mmmm")&" "&year(today())


0

I modified MyFunction to make it work: Function MyFunction(i) 'Code goes here Dim wkb As Workbook Dim wks As Worksheet Set wkb = ThisWorkbook Set wks = wkb.Sheets(i) Dim lColumn As Long Dim iCntr As Long lColumn = 5 'For Each ws In ThisWorkbook.Worksheets 'For iCntr = lColumn To 1 Step -1 wks.Activate ...


0

Modify your formula like this: =IF(COUNTIFS(G$2:G2,G2,C$2:C2,"<="&<max_date>,C$2:C2,">="&<min_date>)=1,1,0)


0

Give this a try: Sub ErrorCleaner() Dim r As Range, rng As Range, L As Long Set rng = Range("L:L").Cells.SpecialCells(xlCellTypeFormulas, xlErrors) For Each r In rng If r.Text = "#N/A" Then L = r.Row Range(Cells(L, "D"), Cells(L, "K")).Clear End If Next r End Sub


0

I think the problem is in your For statements. Right now, the value for r iterates through {G1, G2, ..., G10} and then {H1, ..., H10} and all the way up to {SF1, ... SF10}. It looks like you want to loop through all the cells in L7:L500 so why not do that directly? Dim r As Range For Each r In Range("L7:L500") If IsError(r) Then If r >= 0 ...


3

Assuming your data is in columns A to C, starting at row 2 then you can use this "array formula" in D2 =SUM(IF(FREQUENCY(IF(B$2:B$16<=B2,IF(C$2:C$16>=B2,MATCH(A$2:A$16,A$2:A$16,0))),ROW(A$2:A$16)-ROW(A$2)+1),1)) confirmed with CTRL+SHIFT+ENTER and copied down the column Explanation: This is a common technique used to get a count of different values ...


2

You can do this with an array formula. For a fixed range, the formula in C2 would be =IF(MAX(IFERROR(SEARCH($A$2:$A$5,$B2),0))>0,"yes","no") Be sure to enter this as an array formula using Ctrl+Shift+Enter. You'll know it worked when you get curly brackets { } on either end of the formula. If you want the range to be dynamic, one good way is to define ...


0

Couldn't get the above to work, as the formula would return the largest column (number) that was non-zero. I used =MATCH(SMALL(J10:N10,1),J10:N10,0). The small ignores zero values.


0

Try this small User Defined Function (UDF): Public Function MultiMatch(sIN As String, rng As Range) As String Dim r As Range MultiMatch = "no" For Each r In rng If InStr(1, sIN, r.Text) > 0 Then MultiMatch = "yes" Exit Function End If Next r End Function User Defined Functions (UDFs) are very ...



Top 50 recent answers are included