Tag: excel-2007

Excel Formula Auto-Update When Columns Added

I have two formulas, (currently) one in cell O2 and one in cell P2 which do not auto-update when I add a column (next week they will be in P2 and Q2). For example, when if I right click on column O2 and say add column, the formulas are pushed to P2 and Q2, and […]

Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and

I am trying to merge 12 excel files (each having single excel-sheet) into single consolidated excel file that will have 12 excel-sheets using Microsoft.Office.Interop.Excel liabrary and following error: “Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to […]

How to use a cell value as a row in a formula

How can I use a cell value as a row in a formula? For instance use the value of B1 and B2 to perform an average or sum of those rows? =average([ValueofB1],[ValueofB2])? Thanks

Excel Process Still Running After Quitting Application in VBScript

I have a VBScript that runs every day to sort out an Excel file that is uploaded every night to a shared drive. The issue I am running into is that even after I quit the Excel application, the Excel process is still running in the task manager. I want to make sure that Excel […]

How to filter last updated week in column?

Selection.AutoFilter FIELD:=RNG.Column, Criteria1:=xlFilterLastWeek, Operator:=xlFilterDynamic I have week days in column P which is (9-Nov, 16-Nov, 23-Nov,30-Nov), so I need a code to select the last date that is 23-Nov. and also if i update again to next week which is 7-Dec so it should Filer 7-Dec. i need a result is, whatever the last Monday […]

External Data “from Access” produces different list of available queries/tables depending on user

I have a MS Access 2007 database with a number of tables and queries it is on a shared server; myself and my co-workers can access this file and edit it. In Excel 2007, if I go to Data: Get External Data: From Access I can see a list of the queries/tables and select one […]

Is there a way to mark metadata as read only in Excel

We have a use case to generate Read-Only(such as the document Creation date or Number of characters) metadata for Msexcel file. But whatever metadata we generate using java(apache poi) is editable. I tried adding metadata in custom , but this also editable . Can some one hel me out in finding a way to mark […]

VBA Error 80004005 when changing regional settings

I’ve created a macro in Excel 2007 that takes data from a table and creates many charts (from 8 to 12 charts on a single tab) and the following line doesn’t work if I change my regionnal settings from “English (Canada)” to “French (Canada)”. The line is : shpProject(i).Chart.PlotBy = xlRows Where shpProject is an […]

Equation/ sorting to seperate one long column of data into seperate columns

I have two columns of data in an excel spreadsheet that is listed like, each date has three numbers associated with it. It is shown like this: 1 112 1 123 1 456 2 788 2 989 2 901 What I am trying to do is have the data shown like this: 1 112 123 […]

compare data in 2 excel spreadsheets and then highlight common data in one excel

I have a excel sheet with 2 tabs . In each tab there are some text written. I need to compare values in column_1 in sheet 1 with values in column_2 of sheet2 and in case any similar values present I need to highlight the text in sheet1. Also, suppose I copy some data from […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.