Tag Info

Hot answers tagged

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 ...


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

The reason you're seeing blank rows when you use | more is that though the CSV's encoding is ANSI all right, it has extra Carriage Returns as you can see below: If you omit the | more you end up with a Unicode CSV. Unfortunately Excel seems to have problems with such CSVs, in that on opening them the values aren't separated into columns. There are two ...


1

I would recommend using COUNTIFS, which allows to use multiple ranges and multiple conditions without making the formula statement too complicated. The general format is the below: COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] ) Which for your case be would like this: ...


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


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 ...


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 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

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

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


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 ...


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

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



Only top voted, non community-wiki answers of a minimum length are eligible