Tag Info

New answers tagged

0

My answer focuses on creating a reference to $A$4:A4 that works on all worksheets since that's the part of the reference to which Excel is prepending the unwanted sheet name. (I assume you're not having any trouble referring to the table.) You can solve this problem using the INDIRECT function as follows: On any worksheet, select cell A1 Create a named ...


0

Substitute the cell reference for the names, so "Sales" below is the cell where this is, which might be something like A1, similarly "Open Stock" might be B1. production = sales * 2.5 - opening stock This does not fix the rounding up to the nearest 500. For that try rounded_up_production = ROUNDUP(production/500,0)*500 And for the zero bounds ...


0

As already answered, use: The percentage formatting for the final rendering The data validation for the input restriction BUT: you need to set it up as user defined validation, with a formula like the following, considering D2 is your reference cell: =IF(D2=TRUNC(D2;2);TRUE;FALSE)


-1

The solution above (creating a template) doesn't work if you want the values to be set based on the series name, rather than the series number, because Excel scrambles it when the template is applied, and I need the colors to remain with the series name regardless of whether that series shows up this month or not. This shouldn't be this difficult. I guess ...


0

This will do the trick: Public Sub allergy_copy() Dim wkb As Workbook Dim wks As Worksheet Dim wks1 As Worksheet Set wkb = ThisWorkbook Set wks = wkb.Sheets(1) Set wks1 = wkb.Sheets(2) endrows = False thisrow = 3 While endrows = False If wks.Cells(thisrow, 1) <> "" Then With wks ...


2

You can try to select the cell you wish to paste in, then put your pointer in the edit box at the top and click there to give that focus, then paste, then you can still use the arrow keys in edit mode. It's one extra step, but it works.


0

Something like the following should work: Use transpose to change the single row into a single column. See Switch (transpose) columns and rows Use transpose again to split the single column into multiple columns. See How do I transpose data in columns to rows?


0

This VBa script does it Option Explicit Sub walkThePlank() Dim row As Integer row = 1 Dim total As Integer total = 0 Do While (Range("D" & row).Value <> "") Dim val As String val = Range("D" & row).Value If (val = "-1") Then total = total + 1 Else Range("G" & row).Value = total total = 0 End ...


0

Using VBA, inser a module on the worknook and paste this on the right side: Public Function countNegatives(name As Range) countNegatives = 0 If name <> "-1" Then therow = name.Row thecolumn = name.Column End If endrow = False counter = 0 While endrow = False If therow > 1 Then therow = ...


0

Try this out and see if it works: =SUMIFS(INDIRECT("'"&A1&"'!5:5"),INDIRECT("'"&A1&"'!2:2"),">="&B1,INDIRECT("'"&A1&"'!2:2"),"<="&C1)


0

Excel chart series don't play nice when you try to lay spreadsheet functions into their definitions, as you've probably noticed. One way to achieve what you're describing is through defining helper columns: Here I've constructed a random parabola as example data and laid in one way of constructing the helper columns to allow tuning of the plotted data ...


0

Let's analyze the code real quick - Dim score As Integer score = Range("E2:E841").Value Type mismatch? Or something else? You can't set one integer to the value of a range of cells. You're going to need a loop - Private Sub CommandButton1_Click() Dim score As Range, result As String set score = Range("E2:E841") For each c in score If c ...


0

You might have to change the currency format on windows and that will push every application running on windows to use the currency symbol selected. Step 1: Click start button and head to control panel. Step 2: Click Clock Language and Region. Step 3: Just under Region and Language click Change the date, time or number format. Step 4: In the format tab ...


0

One way of showing more than 8 items in a list is by using the developer tab and inserting a macro Drop-Down List control from the Toolbox and you can then specific how lines you want to show when clicking the drop-down arrow.


4

I believe the color and size are both the Speed of Onset. Vulnerability corresponds to the color of the area under the dot (Green, Yellow, Red). Higher values of V trend towards red squares. Partial Answer: This is as far as I could get before I had to leave for the day: It's a bubble chart. I typed up the data in the sample for points 1 - 12. Next, I ...


0

Have you tried this:1.Click Start, and then click Control Panel. 2.Click Default Programs. 3.Click Associate a file type or protocol with a specific program. 4.Select Microsoft Excel Worksheet, and then click change program. 5.Under Recommended Programs, click Microsoft Excel. 6.If Excel does not appear in this list, click Browse, locate the Excel ...


0

The best approach is to standardize your data. You can use the find/replace command in Excel, Find Mirsini, replace with Mirsini Hotel - but you have to make sure you don't replace Mirsini Hotel with Mirsini Hotel Hotel. This can be time consuming. However, if the names in the two lists are fairly standard, you can write a nested Vlookup using a separate ...


0

Actually it's a very good question I'd also use frequently, but unfortunately currently I don't think there would be a general solution you could use. One workaround would be to use VBA function, but it's also have it's own limitations, below you can find a short function to do it (of course if you want to use it broadly it need to be improved e.g. error ...


1

If you want to get the data out you probably want to use index,match and not address match. Assume your categories are in column A, values in column B (data from row 1 through 15) Max value entered with ctrl+shift+enter =MAX(IF($A$1:$A$15=F2,$B$1:$B$15,0)) To grab the value in column C entered with ctrl+shift+enter ...


1

When you are comparing the columns row by row you can get rid of VLOOKUP Put the following formula in G1 cell: =IF('SUMMARY SLS'!$B1='SUMMARY P&S'!$B1,"X","") then copy G1 cell and paste (I'd reccommend paste-special/formula) on G columns' rows at least for the number of B columns' rows you need to compare. EDIT: In the case you are comparing ...


1

Based on what I have understood...You can explore VLOOKUP function. I am assuming that "X" is content of column somewhere next to the column B in P&S sheet. Sample formula =IF(ISERROR(VLOOKUP(B1,'P&S'!$B$1:$C$6,2,FALSE)),"",VLOOKUP(B1,'P&S'!$B$1:$C$6,2,FALSE)) B1 - Contents of cell B1 (in SLS sheet) VLOOKUP B1 against range of B1 thru B6 (in ...


0

You can either just select all cells and apply the appropriate formatting, or create a style (Main Tools on the ribboon), for instance by creating (duplicating) a new one or modifying an existing one. If you need just some cells formatted this way, you can do some research on so-called conditional formatting. It's a handy function in Excel that allows you ...


1

Try Review > Track Changes for a similar and minimally invasive effect.


3

If VBA is a valid solution: You can use the Change event to automatically colorize your cell if you change values. Example to change text color to purple Private Sub Worksheet_Change(ByVal Target As Range) Target.Font.ColorIndex = 7 End Sub Example to change backcolor to red Private Sub Worksheet_Change(ByVal Target As Range) ...


0

Sounds like a job for subtotals will turn this: into this: which can also be collapsed to show only totals by clicking the number 2 on the left


0

This should work. Put in in a module: Public Sub sheetmerge() Dim wkb As Workbook Dim wks, wks1, wks2 As Worksheet Set wkb = ThisWorkbook Set wks = wkb.Sheets(1) Set wks1 = wkb.Sheets(2) Set wks2 = wkb.Sheets(3) emptyRow1 = False i = 1 k = 1 ' Loops while there is data on sheet 1 column 1 While emptyRow1 = False found = False ' extracts the name ...


0

From the formula you supplied : =IF(ISERROR(MATCH(B6,$C$6:$C$810,0)),"",B6) The cell will display a blank on error because its being told to (the "" part of the formula). Try changing it to : =IF(ISERROR(MATCH(B6,$C$6:$C$810,0)),C6,B6)


0

this would require a not so difficult VBA script. Here is the pseudo code Create Array that contains All Names from Sheet B For i = 0 to CellFromACount IF A.Name is not in ArrayB SheetB.AddLine (Item) Next In order the write the code, follow some tutorial by searching Excel vba tutorial on a search engine. Example here: VBA Tutorial The things you ...


2

Click Insert -> Pivot Table -> Tick Date_TY, PropDemTY, PropRevTV in sequence -> Drag STR# to Column above Date_TY, the settings should like this... (don't have English excel installed, just match the position is ok)


0

This is what reliably works for me. Although I cannot swear I don't apply Number format first. Number Format alone definitely does not work most of the time. Sometimes Text-to-Columns causes some weirdness. This works, but you have to select the range in question and do a little VBA: Sub ...


0

Excel stores dates as a sequential number, starting with the number 1 for the date 1 January 1900. You say you're seeing 6-digit numbers. The smallest 6-digit number is 100000, which in the 1900 date system represents the date 14 October 2173. I don't know what data your employee spreadsheet contains, but unless you're actually expecting the data to ...


0

Try this formula to return serial numbers In File A worksheet 4 that are matched in the File B worksheet 2: =IF(ISERROR(MATCH('[FileA.xlsx]Worksheet 4'!G1,'[FileB.xlsx]Worksheet 1'!$E:$E,0)),"",'[FileA.xlsx]Worksheet 4'!G1) Identified are the two Files with their relevant columns that contain the data to be compared. Create a new check column in your ...


0

Try this formula: =MID(A1,SEARCH("$",A1,IF(ISNUMBER(SEARCH("Save",A1)),SEARCH("Save",A1),1)),SEARCH(" ",A1&" ",SEARCH("$",A1))-SEARCH("$",A1)) Note: It works only if you have '$' before the value


3

"put them into another cell... leaving only the $x.xx price" You can't change your original cells in Excel with standard formulas, what you can do is to extract the price into a new cell / column. To extract first $..: =MID(A1,FIND("$",A1),FIND(" ",A1,FIND("$",A1))-FIND("$",A1)) This function looks for the first $ and extract the text from that ...


0

Couldn't you just create a separate worksheet and paste each column into it and sort both columns? I remember there being another way but I do not remember, I will update this If I remember.


0

It seems you may have a reason for creating an app to do this, it's difficult to be certain. However, unless you do have a special reason you haven't told us about, you can achieve what you're asking using built-in browser behaviour that occurs automatically when clicking a link. For example, you might have the following link in your web page: <a ...


1

This should work (change to your preference) - Sub Macro1() Dim LastRow As Integer LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ActiveSheet.Cells.Borders.LineStyle = xlNone For Each c In Range("A1:A" & LastRow) If c <> c.Offset(1) Then With Range(c, c.Offset(, 2)).Borders(xlEdgeBottom) ...


-2

An example using a macro can be found here: Macro to colour part of the text in cells in Excel Excel Macros - For Loop to Colour Part of Cells Use an Excel macro that contains a for loop to loop through rows of weather data and colour part of the cell text red if it contains the word hot and blue if it contains the word cool: Instructions ...


0

A simple example: put a data validation pull-down in A1 put some date in B1 put =10*COLUMNS($A:A)+LOOKUP($A$1,{"AFAM","AFCM","MSM","MSM/FW"},{1,2,3,4})+$B$1 in C1 copy C1 to D1 and E1


1

Simple words explanation: sub four() must have a corresponding end sub before the line that begins with Function OverFour(...


2

Your request is pretty vague, so here's a pretty vague answer that will hopefully get you on the right track. In each cell C1, D1, and E1, your formula will have to include some check for the value in A1. You can use the following formula construction and fill in the specifics for your project (i.e., replace the parts of the formula in <...>). ...


1

Suggestion for bare Excel: Adjust first column and row width and height to get column two and row two into correct position. Hopefully you can match the row height (of the following lines) to the line height on the form and column width to also match the form in a similar way. If that is not possible, then you might need to continue in the initial style, ...


1

You try to define a function within a sub, it isn't possible in VBA.


1

As far as I'm aware, the color of the Freeze Panes divider line cannot be customized with either the Excel user interface nor the VBA object model. Don't shoot the messenger.


0

I could find a solution only with helper column: In column Y enter: =Aggregate(3,5,X36), this will result 1 for visible rows and 0 for hidden ones (you'll see only 1s, but don't worry, it works). Formula to J13: =SUMIF($X$36:$X$16837,I12,$Y$36:$Y$16837) Formula to I13: =J13/SUM($B$36:$B$16837)


0

If you put the different series under each other, with the X's in the same column, but the Y in consequtive columns, Excel interprets the data exactly as you describe. Dependent on the way your original data is organized, there are several options to automatically arrange your data this way. Here is my example, with 2 data series (random x's and sin(x) for ...


0

The easiest way to do this is to use a font which is a barcode. I've done this and used the Code 39 from http://www.barcodesinc.com/free-barcode-font/


1

As an array formula try =SUM(IF(((A1:A100>1)*((ROW(A1:A100)<20)+(ROW(A1:A100)>40)))>0,1,0)) You can also do this CSE free with =SUMPRODUCT((A1:A100>1)*((ROW(A1:A100)<20)+(ROW(A1:A100)>40)))


0

On VBA insert a module and on the module this code: Function OverFour(Target As Range, top_value As Integer) As Integer thisrow = Target.Row 'row of the order cell thiscolumn = Target.Column 'column of the order cell thisvalue = Target.value 'value of the order cell thisorder = Cells(thisrow, thiscolumn) notSame = True newrow = ...


0

One way, using VBA, is to: Assign a named range to the cell that holds your Drop Down List box. Check the Named Range value using Instr(). If true, hide a column. If InStr(ActiveSheet.Range("DDLB").Value, "Marketing") Then Columns("D:D").Hidden = True End If



Top 50 recent answers are included