Excel How to calculate sum of cells from chosen column which meets chosen conditions

I have an Excel table which contains 10 columns with object parameter values, every column has a label in the first row with a parameter name, and in the 11th column I have indicated the class to which the object belongs. I am trying to make a formula which can calculate the sum of parameter values for an object given a parameter name and class name. I need to chose a parameter name in a list field and class name in a list field and get the sum.

Deleting some data using Excel VBA and ranges

I'm looking for some guidance with a task I have been set from my eager new boss! I've got a list of error codes in a named range "List", and a list of specific error codes that need to be identified "Codes". What I need is some VBA code that will will check "List" and if any code exists that isn't in the "Codes" list it will delete it. (So, if it's in the "codes" range it stays, otherwise it gets deleted). Can anybody help me with this please? So far I've got this code, but it only does the

Sum elements in matrix in Excel

Given the above matrix, I want to create a new matrix, which sums the numbers within the first matrix for a given id in a given year (a,b or c). So for the above, it should look like: 1 2 3 a 0 0 1 b 0 0 2 c 0 0 4 Can anyone see what to do?

circular reference excel formula makes VBA method return 0

I writing a VBA plug in function to perform rectangular rounding of a series. In my VBA method I'd like to detect if there are empty cells above the cell containing the formula/VBA method. But, if I use ActiveCell in my method Excel complains of circular references and returns 0 instead of the return value of my method. Example method: Function MovingAverageSmooth(r As Range, m As Integer) ' returns a smoothed average using the 'rectangular' method Dim cStart As Long, x As Long, total

F# Excel UsedRange is not Defined

Error: "The field, constructor or member 'UsedRange' is not defined". Code excerpts: open Microsoft.Office.Interop.Excel open Microsoft.Office.Tools.Excel // Onorio's excellent suggestion didn't fix code until this line was added. // Once the above line was added, Onorio's suggestion worked, // but now F# says "The field, constructor or member 'EntireColumn' is not defined". let xl = ApplicationClass() xl.Workbooks.OpenText(... let wb = xl.Workbooks.Item(1) let ws = wb.ActiveSheet :?> Wo

Excel How can I delete a Column of XLSX file with EPPlus in web app

How can I delete a column of a .XLSX file with EPPlus in a web application? I use EPplus for generating Excel reports and a stored procedure to get the data from database. The problem is that I want to remove one of the columns of information in the report file by EPplus (stored procedure should not be changed.) I would remove the additional column in and also want to change the page layout direction to (right to left), but it does not work '----/// Click Report Button ///---- Protected Sub

Excel Copy-paste doesn't trigger worksheet_change

Here is what I need to do: When I have written something into a cell in the sheet, my Worksheet_Change code should check if the cell contains certain characters and then replace those characters. That part of the code is working fine. However there is a slightly odd behavior. Here's the code so far: Private Sub Worksheet_Change(ByVal Target As Range) Target.Value = Replace(Target.Value, "ß", "ß") MsgBox "This is the value: " & Target.Value End Sub What's happe

Excel ABAP GUI_DOWNLOAD issue where characters turned to decimal format E+

Program is using GUI_DOWNLOAD then save to excel file. But upon opening the excel file, some cells which is expected to have sample value "1E9" is turned into data "1000000000" or "1.00E+09". Sample comparison between excel & (screenshot) SAP screen Turns out any variable with data type char3 with this format number + E + number is converted into an actual number. Is there any way to fix this issue so that the downloaded version shows the ACTUAL expected data (i.e. 1E9 from sample given)

Excel What is a Compact way to Refer to a Set of Non-Contiguous Rows?

The below code seems inelegant and wasteful. Is there a more compact notation for referencing all those rows? Sub HideEBRedData() 'un/hide EIRP Budget rows with redundant path data Application.ScreenUpdating = False Set EIRPBudget = Sheets("EIRP Budget") With EIRPBudget .Rows(29).Hidden = Not .Rows(29).Hidden .Rows(30).Hidden = Not .Rows(30).Hidden .Rows(50).Hidden = Not .Rows(50).Hidden .Rows(51).Hidden = Not .Rows(51).Hidden .Rows(54).Hidden = Not .Rows(54).Hidden .R

Excel Dynamic Chart using named range that can be empty

I have chart in excel that is driven entirely by dynamic named ranges (using OFFSET/COUNT formulas). These named ranges will under certain conditions be of zero length. As the charts are trying to generate based on what is essentially an empty named range excel is throwing the following error: "A formula in this worksheet contains one or more invalid references" I know that it is the named ranges causing the error and I dont want to remove them, but I'm having a hard time thinking of a way ar

Excel VBA Coding to pull data

I have 50 .xls files saved on a shared drive by the name of users. Eg: "Rahul Goswami.xls", "Rohit Sharma.xls", etc. Each Excel file contains 2 worksheets: "Case Tracker" and "Pending Tracker". In the "Case Tracker" worksheet users put their daily data/ daily production. I wanted VBA code to pull the entire "Case Tracker" worksheet from all 50 Excel files in one separate Excel workbook, one below the other. Currently I am copy-pasting the data from the Excel files to the master workbook to "

Lock row to specific cell in Excel

I have B1:B501 column that includes P/N's. The column auto-sorted by array formula. If i add new item in my BOM, the B column automatically resorted (A-Z). Each part have 100 cell for price. B1 part have C1, D1, E1...; B2 part have C2, D2, E2... for price that i get from suppliers. The problem happens, when i add new part to my BOM. The B column automatically resorted, and a new part can enter in the middle of the B column. This mixes between rows (prices of Bx part can move to By part). I need

Excel CDate fail parsing a valid date

Previously I wrote a VBA script which used CDate and it works perfectly on my older Macbook (non-retina one). While today I try the script on my retina Macbook Pro, and the script always failed on the line related to CDate or actually anything related to date. Using Mac Excel 2011 (14.5.6) I tried a simple script found online for parsing date: Sub thisWillFail() Dim LstrDate As String Dim LDate As Date LstrDate = "Apr 6, 2003" LDate = CDate(LstrDate) ' Will fail here End Subl

Excel I do not understand error 1004

Why do i get error 1004 here: ILast = .Cells(Rows.Count, 1).End(xlUp).Row I tried Sheets("MFG_DATA").Cells(Rows.Count, 1).End(xlUp).Row And then I get error 9. This is the loop it is in, With DCSPROGRAM.Sheets("MFG_DATA") ILast = .Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To ILast If .Cells(i, 1).Value = MFG Then FORM.Cells(8, 2) = .Cells(i, 3).Value I have a loop very similar to it and it does not trigger error 1004: With Wbk.Sheets("QTR_LOG") ILas

Fastest way to append to an existing excel file through visual studio

I am very new to visual studio (currently using Visual Studio 2013) and I am trying to append strings from my list to an existing excel file. Example item in list = "item1,item2,item3,item4" I am trying to append each item to each cell starting from the last row of the excel file. The below code works but it is taking too much time on a lower spec pc, Any idea on how to make it faster? Or are there any better way to append from my list? I am not familiar at all dealing with excel.application obj

Excel count orders where 2 products appear

I've been struggling with this one for a couple of day's. I have a table with orders and products. What i'm attempting to do is count the amount of times 2 products appeared together on an order, the table looks someting similar to this; table orders I need to count the number of orders that e.g. 'jacket' and 'jumper' were on the same order. The output table looks something like this; output table For context, i'm trying to understand how best to set up related products for a website proje

Excel: Moving Specific Text to Another Row / Column

Good afternoon, I am having some difficulty separating data that is in one column. I have tons of rows, but the person that uploaded the data merged everything into one column. The entire column has education, age, male, female, and blood type. I want to take the patients attributes and put them into five columns. Is there a way I can move data into five columns without using "text to columns" in "data" in Excel? Any help would be appreciated. Thank you. EducationAge MaleFemale Bloodtype

How do I change text color in Excel when an IF statement evaluates to true

I have the following formula: =IF(OR(date1="",date2=""),21,date2-date1) I am familiar with conditional formatting, but in this case I am pretty sure it won't function to correctly to do what I want to do. I want the 21 to change to aquamarine, BUT only if that is because the formula evaluated the OR statement as true. If the date2-date1 is 21 I DON'T want it to change colors. Is it possible to do this?

Very small Power Query hangs excel if source table is changed

I have a power query that I'm using to automatically import and filter a table in the same workbook into another sheet. It only 120 rows, I remove inactive rows and remove a few columns. If I go to the source table and change a single value, then refresh the query, it processes in a couple seconds. If I change almost all the values for a column (ie, swapping "Yes" to "No" and vice versa), refreshing the query will hang Excel for 30+ minutes. What is causing this, any ideas on how to debug, or

Optimizing Excel CountIfs - can I make it go faster?

I have some larger files I need to validate the data in. I have most of it automated to input the formulas I need automatically. This helps eliminate errors of copy and paste on large files. The problem is with this latest validation. One of the latest validations involves counting the number of rows that match 3 columns. The 3 columns are in Sheet 2 and the rows to count are in Sheet 1. Then compare this count with an expected number based on Sheet 2. It is easy enough to do with CountIF

Excel Subtotal max of visible cells, return text value in row of highest value

I'm attempting to write a formula to find the most recent date of an inspection in a filtered data set. These inspection dates are in Sheet1!I6:I16627. Once the most recent date is identified in the filtered data set (I want to look at visible cells only), I would like to pull the name of the inspection that is associated with the most recent inspection date. Inspection names are stored in Sheet!R6:R16627. I was trying to build a formula using =Subtotal(104,I6:16627) but was unable to determine

Excel Autofilter to hide rows if cell above is blank

As the title suggests, I am looking for a way to hide a row if the above row is blank, otherwise un-hide that row. It seems that it should be simple but I can't seem to do it. After research I did come across this code that looks to me it should work, however I am not getting any response out of it. Range("A15:A21").AutoFilter 1, "<>", , , False The range is correct in the above code.

Excel Highlight changed results

I found this vba a few months ago and very useful. Thanks to Edward Bagby. Now I want to know how to highlight the changed results? Not the text, but only the cell interior. Can someone help me? I tried to add after rngName.Copy Destination:=Worksheets("Sheet2").Range("E" & i) some solutions like these: ActiveSheet.Cells.Interior.ColorIndex = red or rng1.Interior.ColorIndex = red but nothing to change. Thank You in advance!

Excel VBA Last Change Method

I am looking for a function to print in a comment box, who was the users that changed the data from that cell. What I have for now is this: Private Sub Worksheet_Change(ByVal Target As Range) If Range("A" & Target.Row).Value = "" Then GoTo EndeSub If Not Intersect(Range("C:JA"), Target) Is Nothing Then On Error GoTo EndeSub Application.EnableEvents = False Range("B" & Target.Row) = Now End If EndeSub: Application.EnableEvents = True End Sub It "triggers" au

Excel Comparing two columns of different groups having 4 columns in each group, and inserting empty row wheren the column do not match

Comparing 2 columns from 2 different groups having a empty column between the groups , each group containing 4 columns, inserting the cell in every column of a group 1 if column D of group 1, do not match with column I of group 2 or inserting the cell in every column of group 2 if column I of group 2, do not match with column D of group 1.

Copy part of row if value is met in Excel

I have an Excelsheet looking similar to this (Sheet1): What I want is that for all rows (which is dynamic) that Column J contains NEW-LOCATION I would like to copy the information on those rows from column A, C, D and E to another sheet (Sheet2) but I would also be able to add new information on the new sheet like below: The green part is copied from Sheet1 and the Yellow part is stuff I write myself in Sheet2. It should be dynamic and if the value NEW-LOCATION is removed in Sheet1 the row

Excel Issue with CONVERT()

I am trying to do a CONVERT() excel formula to convert feet and inches to metres and I am trying to use the CONVERT formula. =(CONVERT(G17,"ft","m"))+(CONVERT(H17,"in","m")) The ft part of the equation works but the in does not, can anyone explain my mistake?

Excel bar chart with two y axis & split areas

I have data like this. source table I would like to make a bar chart like below. Chart I want The problem is that since Dioxin ranges are way smaller than the others, I can't look at the dioxin data properly. In order to solve this problem, I would like to separate the chart areas into two and make a line between two areas with ranges of Dioxin shown in the right y axis. How do I do this?

Excel Moving location of pivot table(s)

How can I move the location of a pivot table in order to ensure it will not overlap with another pivot table below on refresh? I have 10 pivot tables whose column span is always equal to A:E. The rows will dynamically update as the pivots refresh. I want there to be 2 blank rows before each new pivot table starts so I am using the following workaround (other questions indicate you cannot directly insert rows as a pivot table refreshes to stop them from overlapping which is the root problem I

In Excel, how do I conditionally format a cell after matching two distinct values across two separate worksheets?

I was provided a spreadsheet with a list of emails that have been sent from my company. I would like to create a visual timeline to show the days on which these emails were sent (a Gantt chart of sorts, though the duration is only one day for every task). The worksheet, named Email List, includes a table: Column A has list of Email titles (simply named Email), and Column E has the Date (simply named Date) it was sent (there are many other columns, but these are the only two I need to reference

Excel How to unite value in a column using lookup function

I have an example with two columns (Name, Region), column (Segment) is the result I have solved. The condition for column "Segment" is IF(AND(John = "North", John ="South", "John"=Centre"), "N-S", IF(OR(AND(John = "North", "John"=Centre"), John="North"), "N", IF(John="South", "S"))) I can't find any way to solve this problem. I hope anyone can help me. Thanks!

Excel VBA - best practice to call a function upon detecting a change in cell value

I've experimented with many different ways to automatically call a macro/function upon detecting a change in a certain range of cells Something along the lines of: Private Sub Worksheet_Change(ByVal Target As Range) Range ("PartNumbers") = "B6:B18" If Not Application.Intersect(Range(Target.Address), Range("PartNumbers")) Is Nothing Then Application.EnableEvents = False decodepartnumber (Target.Address) Application.EnableEvents = True End If End Sub I'm wond

How to add rows to excel document with formulas using powershell

I have a small Excel template, the first row consists of cells with formulas like =SUBTOTAL(9,C3:C15000) and such. The second row is headers. I have a PowerShell process gathering data to fill the Excel daily using Import-Excel, but when I merge the two files (importing the template Excel into a variable and adding rows to it), those top cells with the formulas lose their underlying formula values and get read as 0 (since there is no data in the template). Is there a way to preserve thos

is there a way to sort data depends on a value on excel

I want to put some excel in row if they contain a specific word A |B ____________________ HP SERVER | 1 HP LAPTOP | 2 DELL SERVER | 2 LENOVO LAPTOP | 3 HP SERVER G8 | 2 I want the result to be sorting all SERVER in row like this A |B ____________________ HP SERVER | 1 DELL SERVER | 2 HP SERVER G8 | 2 HP LAPTOP | 2 LENOVO LAPTOP | 3 is there any way to do that with formula or VBA I am using Microsoft Office 2013

Excel Type Mismatch Error in VBA code during solver optimization

I am getting a type mismatch error on my excel and vba based optimization problem. I am using Frontline Solver trial edition which I used before last year. When I run the code (which was a slight modification of my earlier trial) I get this error.I know that it may occur when there is an error in excel cells, format or content. In my code, initially, I set the range which will be changed to find the solution to zero. The error comes up in the line below SolverOK SetCell:="$H$31", MaxMinVal:=1,

Excel Subscript out of range error when using Worksheets.Activate syntax

I have written the below code and when debugging getting an error "Subscript out of range" for the line Worksheets("Program Mapping Sheet").Activate Application.DisplayAlerts = False 'Don't display alert Worksheets("Program Mapping Sheet").Activate 'Set the active worksheet Sheets("Program Mapping Sheet").Cells.Clear Worksheets("Copied Raw Data").Activate 'Set the active worksheet Worksheets("Copied Raw Data").UsedRange.Copy 'Copy the data Worksheets("Program mapping sheet").Activate 'Set t

Using AUTOFILTER to copy filtered data to the bottom of a list (different Excel Sheet) without Header Row

I am using AUTOFILTER to filter data on one Excel worksheet (Input_Wkr_Hrs) and append the data to the bottom of a list (table) on another worksheet (Output) in the same workbook. The problem I have is that when the data gets added to the bottom of the list, it includes the HEADER ROW Names. How do you copy filtered data using AUTOFILTER and not include the HEADER NAMES when appending to the bottom of a list? The source table on the worksheet (Input_Wkr_Hrs) has three columns (Emp Name, Section

Excel calculate value for new category based on other group categories

I am getting data from a database that is provided in long format and I need to get ratios from values that are given different categories. E.g. I want the average price based on revenues and quantity sold. Is there an easy way to calculate this in a pivot once I have the data? My MWE would look like this And I woul like to calculate the new rows with the category price One way would probably to do this in MS SQL beforehand, but I am not that skilled with that and I need my colleagues to be ab

How can i change a Date value in excel using OLE DB

I'm trying to update the date in a cell of excel using OLEDB, I can change other fields, but I'm having problems with the Date format. cmd = new OleDbCommand("UPDATE [" + hoja + "$A4:V104] SET " + valoresAActualizar[0] + "= " + "'" + valoresAActualizar[1] + "'" + "where " + valoresAFiltrar[1] + " " + valoresAFiltrar[2] + " " + valoresAFiltrar[3], conn); This query works, but not with the date. I don't know if ther

Excel - SUMIF Function

I made a worksheet covering meal expenses for a set period of time (01-08-2020 until 31-08-2020.) The company will pay up to 75kr, Monday-Friday. The list covers expenses 7 days a week, If the amount is greater than 75 on Monday-Friday, I have to pay the extra expenses. (This I have figured out correctly). Saturday and Sunday I have to pay fully. So my question is, how can I sum this and exclude Saturday and Sunday? Attempt 1: I tried this formula (Norwegian excel. Summerhvis = SUMIF. Lørdag = S

Excel Using the LEFT function to extract everything before a number doesn't work well with spilled arrays

I am currently trying to extract the prefix of a store ID to be able to then generate a list of stores with only that prefix. Cell D1 has that formula to extract the unique prefix : =TRANSPOSE(UNIQUE(LEFT(C2#, MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2#&"0123456789"))-1))) Cell C2 has that formula to extract the unique store ids from another sheet : =UNIQUE(INDEX('Male Shoes'!A1#,,6)) The problem is that the formula in D1 only returns the first two characters from all the unique prefixes in

  1    2   3   4   5   6  ... 下一页 最后一页 共 479 页