Search Results

Search found 4866 results on 195 pages for 'excel ranges'.

Page 45/195 | < Previous Page | 41 42 43 44 45 46 47 48 49 50 51 52  | Next Page >

• Excel 2007 - combination of If and vlookup formula

  - by Neo

  i have a cell that refer to more than 1 worksheet and display the result (value) when it found the product from the 2 sheets. SheetA has 2 columns which column A is the value and column B is that product name, SheetB only has product name. Below is my formula but it failed to display result of product value, instead it always display Not Found even though the product is found from the sheets, is there anything wrong with the formula ? =IFERROR(VLOOKUP(A35,'SheetA'!A:B,1,FALSE),IFERROR(VLOOKUP(A35,'SheetB'!D19:D115,1,FALSE),"Not Found"))

  Read the article

• Excel 2010 - more than 1 calculation within an IF() statement

  - by Da Bajan

  I have a situation where I need to calculate shipping values based on the length of the supply chain. Easy, however I need to have instances where an increased amount is required based on specific date criteria. My example is as follows: Shipvalue = 100 Date1 = 1/1/2013 (Jan) - ship 50% more than usual Date2 = 2/1/2013 (Feb) - ship 25% more than usual Date3 = 3/1/2013 (Mar) - ship 25% more than usual Supply chain length is: June - October 100 days November - March 140 days April - June 100 days The issue I have is that as there is an increase in the number of days, my formula: IF( Date1-(Supply chain length + any extra days)=today's date, shipvalue+(shipvalue X 50%), IF( Date2-(Supply chain length + any extra days)=today's date, shipvalue+(shipvalue x 50%) IF( Date2-(Supply chain length + any extra days)=today's date, shipvalue+(shipvalue x 50%), IF( preceding cell<>0,shipvalue, 0) ) ) ) Now the problem with this is that if the length of the supply chain increases then the formula misses all but the 1st increase. So, I thought of adding a variable that would be incremented and checked every time you made an increased shipping amount. So, how do I do both the calculation for the increased shipping value, and set the variable in one part of the IF statement?

  Read the article

• Excel VLOOKUP using results from a formula as the lookup value [on hold]

  - by Rick Deemer

  I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the "RO", and some have 5 digits. To do that I used =REPLACE('RAW DATA'!A3,1,2,"") Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match, and I need it to return that value to the cell. I have tried using INDIRECT in different ways to no avail, and I'm not sure that I fully understand its usage. So at this point I am Googling for a method to do this and at a standstill.

  Read the article

• Recomposing data structure in Excel

  - by Velletti

  I've got a sheet of 35k rows of the following kind of data that I want to reshape into table below. So, I want to reshape this data in a way to get all the people within a specific GroupID in separate columns. I suppose that I should add a counter for each row within specific group id? Also, I suppose these kind of issues are a lot more comfortable to be done in databases? Since I often have this kind of data, I need be much quicker about solving it, then I am now.

  Read the article

• MS Excel convert cell matrix (1x15 -> 3x5)

  - by Stan

  What is the formula for display A1, A3, A5, A7...increasing by a certain step? Moreover, would it be possible to transform below a list (1x15) of data into a table(3x5). Thanks.

  Read the article

• EXCEL 2007 macro

  - by Binay

  I have a macro which connects to db and fetches data for me and makes it comma separated. But the problem is the comma is getting appended to the last row, which I don't want. I'm struggling here. Could you please help out? Here is the part from the code. If cn.State = adStateOpen Then Rec_set.Open "SELECT concat(trim(Columns_0.ColumnName), ' ','(', 'varchar(2000)' ,')') columnname FROM DBC.Columns Columns_0 WHERE (Columns_0.TableName= " & Chr(39) & Tablename & Chr(39) & "and Columns_0.Databasename=" & Chr(39) & db & Chr(39) & ")ORDER BY Columns_0.Columnid;", cn 'Issue SQL statement If Not Rec_set.EOF And Not Rec_set.EOF Then Do Until Rec_set.EOF For i = 0 To Rec_set.Fields.Count - 1 strString = strString & Rec_set(i) & "," Next strFile.WriteLine (strString) strString = "" Rec_set.MoveNext Loop Here is the result I am getting. EMPNO (varchar(2000)), ENAME (varchar(2000)), JOB (varchar(2000)), MGR (varchar(2000)), HIREDATE (varchar(2000)), SAL (varchar(2000)), COMM (varchar(2000)), DEPTNO (varchar(2000)), I don't want the last comma.

  Read the article

• how do I stop excel from adding double quotes to my formula

  - by Alex

  this works: {=MEDIAN((Table1[MonthFinish]=201012)*(Table1[Days]))} but if I put 201012 into cell A3, this doesn't done work: {=MEDIAN((Table1[MonthFinish]=A3)*(Table1[Days]))} when i do Evaluate Formula on the 2nd one...I see that there are double quotes about the 201012 that was pulled from A3...like so: {=MEDIAN((Table1[MonthFinish]="201012")*(Table1[Days]))} and as such, all the 201012s pulled from the MonthFinsh row come back as FALSE when compared to "201012" (ie, 201012="201012" ) where as they come back as TRUE when I hard code 201012 as it shows up as 201012=201012. how do i get even to not put those quotes around the number?

  Read the article

• Copy/Paste including Hidden Columns when Filtering Rows in Excel 2010

  - by hudsonsedge

  I suspect the solution will be related to this question?? I have a spreadsheet that comes to me pre-formatted with hidden columns sprinkled in multiple places (for viewing brevity's sake). I need to turn on filtering, apply a filter to one of the columns, and then paste the resulting rows to a new sheet - including the hidden columns (lather, rinse, repeat). I'd prefer to not undo/re-do the hidden columns unless I have to. Is it possible to paste the hidden columns without adding the extra steps?

  Read the article

• Excel 2010 Move data from multiple columns to single row

  - by frustrated529

  So frustrating! I get data sent to me and it looks like this: a 1 a --2 2 a-------3 3 b 1 b-- 2 2 b ------ 3 3 b------------ 4 4 and i need it to look like this: a 1 2 2 3 3 b 1 2 2 3 3 4 4 I have about 30 columns that needs to move to the top value in their group, then removing the duplicates. I have been searching forums for several days and trying bits and pieces of code. I am having such a tough time with VBA!!!!

  Read the article

• Excel macro to delete empty rows isn't stopping

  - by RocketGoal

  How do I make it stop after a certain amount of rows? I've taken a VBA course and my teacher explained how to delete empty rows. I'm now trying to put this in place but my macro isn't stopping. I thought I had limited it to 200 rows. I'm missing something important. Any pointers much appreciated. Sub RemoveRows() ' Remove rows from last blank cell Dim LastRow As Long Dim ISEmpty As Long 'Count how many records in the list. This is done so that the Do loop has a finish point. LastRow = Range("A200").End(xlUp).Row 'Start at the top of the list Range("A1").Select 'Loop until the end of the list Do While ActiveCell.Row < LastRow 'Assign number of non empty cells in the row ISEmpty = Application.CountA(ActiveCell.EntireRow) 'If ISEmpty = 0 then delete the row, if not move down a cell into the next row If ISEmpty = 0 Then ActiveCell.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop End Sub

  Read the article

• count the number of times a substring is found within a date range in excel

  - by ckr

  I have a spreadsheet that contains test data. column A has the test name and column B contains the test date. I want to count the number of times that the string Rerun is found within a certain date range. For example A B test1 11/2/2012 test2 11/7/2012 test1_Rerun_1 11/10/2012 test2_Rerun_1 11/16/2012 I am doing a weekly report so want to show how many tests had to be rerun in a particular week. so in the above example: week ending 11/2/12 would return 0 (look for dates 10/26/12 and <=11/2/12 with substring "Rerun") week ending 11/9/12 would return 0 (look for dates 11/2/12 and <= 11/9/12 with substring "Rerun") week ending 11/16/12 would return 2 (look for dates 11/9/12 and <=11/16/12 with substring "Rerun")

  Read the article

• Excel formula on IF

  - by Ashvin

  I am looking for a IF formula with 3 conditions. Request for help. Conditions: If Cell value is less than 10000 the value should be 0 If cell value is greater than 10000 and less than 15000, the value should be 150 if the cell value is greater than 15000, the value should be 200. I am trying the below formula but for cell value less than 10000, The value is showing as "false" =IF(cell>10000<15000,150,IF(cell>15000,200,0))

  Read the article

• Easily Plotting Multiple Data Series in Excel

  - by John

  I really need help figuring out how to speed up graphing multiple series on a graph. I have seperate devices that give monthly readings for several variables like pressure, temperature, and salinity. Each of these variables is going to be its own graph with devices being the series. My x-axis is going to be the dates that these values were taken. The problem is that it takes ages to do this for each spreadsheet since I have monthly dates from 1950 up to the present and I have about 50 devices in each spreadsheet. I also have graphs for calculated values that are in columns next to them. Each of these devices is going to become a data series in the graph. E.g. In one of my graphs I have all the pressures from the devices and each of the data series' names is the name of the device. I want a fast way to do this. Doing this manually is taking a very long time. Please help! Is there any easier way to do this? It is consistent and the dates all line up. I am just repeating the same clicks over and over again Thank you!

  Read the article

• Connecting to Microsoft Excel using Oracle Data Integrator

  - by julien.testut

  The posts in this series assume that you have some level of familiarity with ODI. The concepts of Topology, Data Server, Physical and Logical Architecture are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details. In this post I will describe how a Microsoft Excel spreadsheet can be used in Oracle Data Integrator. Microsoft Excel is one of the many different technologies you can leverage in ODI as a source or as a target. Prepare your Excel spreadsheet Prior to using a Microsoft Excel spreadsheet in ODI we need to specify a name for the different cell tables we want to use. You can have multiple names in the same spreadsheet. First open up a Microsoft Excel spreadsheet, we will need to define a named range.

  Read the article

• .NET ExcelLibrary Export Problems - .XLS corruption

  - by hamlin11

  The library is located here: http://code.google.com/p/excellibrary/ I'm using some basic code to create an .XLS file. When I open the file in Excel 2007, I get the following errors: I click yes, then I get: And just for fun, here's the XML error details (not very helpful) Here's the code that I'm using to generate the Excel file: Dim ds As New DataSet Dim dt1 As New DataTable("table 1") dt1.Columns.Add("column A", GetType(String)) dt1.Columns.Add("column B", GetType(String)) dt1.Rows.Add("test 1", "Test 2") dt1.Rows.Add("test 3", "Test 4") ds.Tables.Add(dt1) ExcelLibrary.DataSetHelper.CreateWorkbook("c:/temp/test1.xls", ds) Note: I added a reference to the DLL provided by the project download page and have an "Imports ExcelLibrary.Office.Excel" to link up with it Any ideas on what the corruption is and/or how to fix it? Thanks

  Read the article

• run-time error '429' activex component can't create object

  - by kojof

  I've created a simple application .Net Class that converts an excel spreadsheet into a a pdf file. I then get a Excel 2007 application to call this dll which works fine on my development machine. However when i deploy it on to a machine that has both the .net framework and excel 2007, i get this error - run-time error '429' activex component can't create object when i try and register the tlb or the dll , i get error below. the module c:\temp\test\Printlibarary.dll2 was loaded but the entry-point dllregisterserver was not found. Make sure that ...... is a valid dll or ocx file and then try again. Can someone please help me to resolve this.

  Read the article

• Scriptom (groovy) leaves Excel process running - am I doing something wrong?

  - by Alex Stoddard

  I am using the Scriptom extension to Groovy 1.7.0 to automate some processing using Excel 2007 under Windows XP. This always seems to leave an Excel process running despite my calling quit on the excel activeX object. (There is a passing reference to this phenomenon in the Scriptom example documentation too.) Code looks like: import org.codehaus.groovy.scriptom.ActiveXObject; def xls = new ActiveXObject("Excel.Application") xls.Visible = true // do xls stuff xls.Quit() The visible excel window does disappear but an EXCEL process is left in the task manager (and more processes pile up with each run of the script). There are no error message or exceptions. Can anyone explain why the Excel process is left behind and is there any way to prevent it from happening?

  Read the article

• How to Create VBA Add-In with Shared Codes for All Excels?

  - by StanFish

  I'm writing VBA codes for multiple Excel spreadsheets, which will be shared with others from time to time. At some point I find there are lots of duplications in my works. So I want to find a way to share codes in a sort of Excel add-in, like the .xla file. But when I tried to save the Excel file containing shared codes as .xla file, I got some problems: The file cannot be edit anymore after I save it in the default add-in folder If I move the .xls file to a folder other than the add-in folder, and open it directly - I cannot use its classes - which creates problems for sharing the codes Any ideas to create add-ins in a flexible and powerful way please? Thanks a lot for the help

  Read the article

• Reading Excel Files In C# Always Results In System.__ComObject?

  - by Soo

  This is the code I'm using to read an xls file: Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(1); MessageBox.Show(excelSheet.Cells[1,1].ToString()); Which results in a message box with: System.__ComObject Not sure what's going on, I'd really appreciate any help, thanks!

  Read the article

• OpenXML SDK: Make Excel recalculate formula

  - by chiccodoro

  I update some cells of an Excel spreadsheet through the Microsoft Office OpenXML SDK 2.0. Changing the values makes all cells containing formula that depend on the changed cells invalid. However, due to the cached values Excel does not recalculate the formular, even if the user clicks on "Calculate now". What is the best way to invalidate all dependent cells of the whole workbook through the SDK? So far, I've found the following code snippet at http://cdonner.com/introduction-to-microsofts-open-xml-format-sdk-20-with-a-focus-on-excel-documents.htm: public static void ClearAllValuesInSheet (SpreadsheetDocument spreadSheet, string sheetName) { WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, sheetName); foreach (Row row in worksheetPart.Worksheet. GetFirstChild().Elements()) { foreach (Cell cell in row.Elements()) { if (cell.CellFormula != null && cell.CellValue != null) { cell.CellValue.Remove(); } } } worksheetPart.Worksheet.Save(); } Besides the fact that this snippet does not compile for me, it has two limitations: It only invalidates a single sheet, although other sheets might contain dependent formula It does not take into account any dependencies. I am looking for a way that is efficient (in particular, only invalidates cells that depend on a certain cell's value), and takes all sheets into account. Update: In the meantime I have managed to make the code compile & run, and to remove the cached values on all sheets of the workbook. (See answers.) Still I am interested in better/alternative solutions, in particular how to only delete cached values of the cells that actually depend on the updated cell.

  Read the article

• phpexcel write excel and save ?

  - by boss

  code: <?php /** PHPExcel */ require_once '../Classes/PHPExcel.php'; /** PHPExcel_IOFactory */ require_once '../Classes/PHPExcel/IOFactory.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ->setLastModifiedBy("Maarten Balliauw") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); $result = 'select * from table1'; for($i=0;$i<count($result);$i++){ $result1 = 'select * from table2 where table1_id = ' . $result[$i]['table1_id']; for ($j=0;$j<count($result1);$j++) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $j, $result1[$j]['name']); } // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 2007 file $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); // Echo done echo date('H:i:s') . " Done writing file.\r\n"; } ?> The above code executes and save n no of .xlsx files in the folder, but the problem im getting is biggest count(result1) in the for loop executing in all saved excel files. Please give me the solution.

  Read the article

• Passing an array argument from Excel VBA to a WCF service

  - by PrgTrdr

  I'm trying to pass an array as an argument to my WCF service. To test this using Damian's sample code, I modified GetData it to try to pass an array of ints instead of a single int as an argument: using System; using System.ServiceModel; namespace WcfService1 { [ServiceContract] public interface IService1 { [OperationContract] string GetData(int[] value); [OperationContract] object[] GetSomeObjects(); } } using System; namespace WcfService1 { public class Service1 : IService1 { public string GetData(int[] value) { return string.Format("You entered: {0}", value[0]); } public object[] GetSomeObjects() { return new object[] { "String", 123, 44.55, DateTime.Now }; } } } Excel VBA code: Dim addr As String addr = "service:mexAddress=""net.tcp://localhost:7891/Test/WcfService1/Service1/Mex""," addr = addr + "address=""net.tcp://localhost:7891/Test/WcfService1/Service1/""," addr = addr + "contract=""IService1"", contractNamespace=""http://tempuri.org/""," addr = addr + "binding=""NetTcpBinding_IService1"", bindingNamespace=""http://tempuri.org/""" Dim service1 As Object Set service1 = GetObject(addr) Dim Sectors( 0 to 2 ) as Integer Sectors(0) = 10 MsgBox service1.GetData(Sectors) This code works fine with the WCF Test Client, but when I try to use it from Excel, I have this problem. When Excel gets to the service1.GetData call, it reports the following error: Run-time error '-2147467261 (80004003)' Automation error Invalid Pointer It looks like there is some incompatibility between the interface specification and the VBA call. Have you ever tried to pass an array from VBA into WCF? Am I doing something wrong or is this not supported using the Service moniker?

  Read the article

• Upload An Excel File in Classic ASP On Windows 2003 x64 Using Office 2010 Drivers

  - by alphadogg

  So, we are migrating an old web app from a 32-bit server to a newer 64-bit server. The app is basically a Classic ASP app. The pool is set to run in 64-bit and cannot be set to 32-bit due to other components. However, this breaks the old usage of Jet drivers and subsequent parsing of Excel files. After some research, I downloaded the 64-bit version of the new 2010 Office System Driver Beta and installed it. Presumably, this allows one to open and read Excel and CSV files. Here's the snippet of code that errors out. Think I followed the lean guidelines on the download page: Set con = Server.CreateObject("ADODB.Connection") con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" & strPath & ";Extended Properties=""Excel 14.0;""" con.Open Any ideas why? UPDATE: My apologies. I did forget the important part, the error message: ADODB.Connection error '800a0e7a' Provider cannot be found. It may not be properly installed. /vendor/importZipList2.asp, line 56 I have installed, and uninstalled/reinstalled twice.

  Read the article

• Implementing Excel 2003 COM Add-in UDF in Asyc Programming model using C#(VS 2005)

  - by Venu

  Hi: I am trying to implement a UDF using Excel COM Add-in(2003) with Visual Studio 2005 in C#. I would like to implement the UDF using async programming. The UDF is a slow operation as its results are fetched from a server. As an illustration(not a real world example),the following UDF works fine without any issue: public double mul(double number1, double number2) { return number1 * number2; } How can I do the same functionality in an async way: For example, I would like the UDF return immediately and later when the results are available from a server, I would like to update the desired cells. // This method returns immediately. public object mul(double number1, double number2) { return "calculating"; } // This method of a worker thread will update the results. public OnResultsAvailable(object result) { // Question: how should I update the cells that triggerred the calcualtions above? } Constraints: I cannot use Excel RTD as I have to work with existing codebase written using Excel C# COM Add-in. Thanks for the help. -Venu

  Read the article

• Powerpoint displays a "can't start the application" error when an Excel Chart object is embedded in

  - by A9S6

  This is a very common problem when Excel Worksheet or Chart is embedded into Word or Powerpoint. I am seeing this problem in both Word and Powerpoint and the reason it seems is the COM addin attached to Excel. The COM addin is written in C# (.NET). See the attached images for error dialogs. I debugged the addin and found a very strange behavior. The OnConnection(...), OnDisConnection(...) etc methods in the COM addin works fine until I add an event handler to the code. i.e. handle the Worksheet_SheetChange, SelectionChange or any similar event available in Excel. As soon as I add even a single event handler (though my code has several), Word and Powerpoint start complaining and do not Activate the embedded object. On some of the posts on the internet, people have been asked to remove the anti-virus addins for office (none in my case) so this makes me believe that the problem is somewhat related to COM addins which are loaded when the host app activates the object. Does anyone have any idea of whats happening here?

  Read the article

< Previous Page | 41 42 43 44 45 46 47 48 49 50 51 52  | Next Page >