I am using excel 2010, windows 7. When i make a new worksheet or update it randomly blanks out lines that already have information in them. It blanks out the row number also.
View 1 RepliesI have been trying to chart temperature differences over 10 day's time for six cities using a line graph. For some reason, the lines are all clustered at the bottom of my graph, with values of 0. My spreadsheet is accurate, without any blank cells. I'm using Excel 2010.
View 1 Replies View Relatedi have done in Word VBA successfully (basically, change the Style for a certain number of lines):
VB:
Dim myRange As Range
Set myRange = ActiveDocument.Range(ActiveDocument.Range.Start, ActiveDocument.Range.Start)
[Code].....
I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.
If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.
i have a question with regards to the shading of a table in excel 2010.The grey/white shading of the sheet should adjust automatically when lines are inserted / deleted...what would be the most efficient way?
View 6 Replies View RelatedI am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.
View 3 Replies View RelatedI merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.
As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)
All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:
company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx
I atteched an example of the full list and the result i want
Excel 2010 / Win 7.
I have some code that imports a csv file into a workbook. This works fine when i use a file named .csv. I was hoping to use a random file name (in this case .bmhs) so that we can determine which files we need to import.
The problem i have is when importing a '.bmhs' file the data comes in column A and is a comma seperated list. If i use .csv then the commas are used as the column seperators (which i need).
impFle = Application.GetOpenFilename(filefilter:="BMHS Files, *.bmhs", Title:="Select Import File")
would give me 1,2,3,4 all in column A
impFle = Application.GetOpenFilename(filefilter:="CSV Files, *.csv", Title:="Select Import File")
would give me 1 in column A, 2 in column B, 3 in column C and 4 in column D.
Is there any way that i can maintain using my own file name (.bmhs) but have excel treat it as a csv file.
I am using lesson 33 of Excel VBA and Macros with Mr Excel as my guideline. The coding is as follows:
Dim WBO As Workbook 'original workbook
Dim WBN As Workbook 'individual data workbooks
Dim WSL As Worksheet 'List of files worksheet
Dim WSN As Worksheet
Set WBO = ThisWorkbook
Set WSL = WBO.Worksheets("List")
Set WSD = WBO.Worksheets("Data")
[Code] ......
I have indicated above with ( ) the beginning of my problem.
From the workbook with the list of workbooks to open, in each case I want to refer to a worksheet in each workbook
named "Report" and grab all the rows with data exept heading (rows 1 & 2) and 9 columns.
How do I get WSN in the code above to include the worksheet named "Report" or do I need different coding. Using Windows 7 & Excel 2010.
I am trying to set up a simple sports picking list using Excel 2010. I would like to be able to carry out several filers and then extract results to a new sheet to allow printing.
For example, there are four available terms and I would like to be able to filter by term/sport and student. Ideally I would like to be able to add a command button once this is working.
I use Excel 2010, 64-bit-version with Windows 7. I have one workbook, where whenever I create a formula that references to one specific sheet, the Excel crashes and cannot be quitted even with Task Manager. I have to restart the whole system.
I do not have any worksheet events in either sheet. This is the same workbook that crashes if all cells are selected from the top-left corner of the worksheet.
I tried by deleting unnecessary COM add-ins but that didn't work.
I need to create 3 or 4 detailed reports from an excel 2010 worksheet.
My worksheet contains data including date, invoice number, company , consultant, days, dollars. There are approximately 100 records in the worksheet.
Requirement 1 :I want to use excel 2010 to automatically generate a series of reports broken down by consultant showing date, invoice number, company, and consultant with totals for days and dollars, when all the records for consultant 1 are listed, then I want to show consultant 2.
Same for any other consultants with a grand total at the end.
My understanding is that excel 2010 makes this process very simple .
I have a workbook with worksheet like 2a, 2b, 2c, 2d, 2e, 3a, 3b...etc Is it anyway i can make a tab which will hide a group of worksheet (like 2a,2b....2e) together and unhide all the worksheet with another click on it? becasue i got too many worksheet to show in the bottom..
View 2 Replies View RelatedI am using Excel 2010 64-bit (with SP2 and all other updates installed).
I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)
I need to sort this worksheet as follows:
1. By background color, so yellow is on top, white is on bottom.
2. Then, within the yellow rows, data in column J.
3. Then, within the white rows, data in column A.
Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.
Is there a way to combine these steps, perhaps with a macro?
I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.
I have an excel file with three worksheets in it. I can happily protect each sheet with a different password but what i want to be able to do is the following:
a. have a global administrator password for all the sheets - so i assume set the same password to protect the whole sheet
b. have individual passwords that only allow the user to insert rows and change data within restricted columns, i.e. can only edit columns A:Z and cannot manipulate any formulas within that range A:Z
I have excel 2010 if that makes any difference.
I need to create a separate PDF file for each Excel Worksheet, each worksheet has a different name.
I am using Excel 2010
where to find a spreadsheet which has a macro to perform the FFT on data with > 4096 points? It seems Excel's internal FFT is limited to this amount. I searched the forum and elsewhere on web and have not found what I am looking for. I wish to enter the data in a column of the spreadsheet, preferably in a named range such as "Data" and have a macro perform the FFT.
View 3 Replies View RelatedI am using excel 2010.
I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.
However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...
How can I update each sheet individually, without compromising the other sheets?
Excel is not responding to Shift - F9.
I have created a worksheet and password protected, I know the password but now want to send to someone and remove the password protection option instead of them having to use a password to open it.
View 5 Replies View Relatedhow to use the offset function to create a dynamic range in Excel 2010. An in-house Excel form I work with spans columns A thru P and has three sections. In Section 2 the user fills in employees who are requesting to work overtime. Section 2 starts row 12 and ends row 61. It's sometimes necessary to add rows to this section. How do I make this section/range dynamic using the offset function?
View 1 Replies View RelatedI have a workbook that filters the data on the worksheet "Reports" this then adds new worksheets and copies and pastes the relevant data that has been sorted, this is done by the name in column "B".
What I need is to rename the new worksheets with the data that is in cell "B2" of the new sheets.
Using excel 2010.
Code:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)
I am using the above formula in my code with two Named Ranges
Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)
=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]
I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.
I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49
I have a workbook that is doing something I don't understand. In several places I am using arrays to transfer/modify information, then placing the array back onto the worksheet, and this works perfectly. The following code... doesn't.
(I've cut out unnecessary code like variable declarations - all my integer items are declared as Long)
Code:
Dim varA As Variant, varB(10000, 1) As Variant
Set wkDest = Sheets("a")
erB = 0
For lpTerm = 1 To 4
[Code]...
and several other ways. What happens is just null output, a full column of nothing. However, when I expand my worksheet range to two columns, all the data from varB appears - it is shifted down one column and row. So my data starts at .cells(2,2) instead of .cells(1,1). The data is all perfect, I've verified it left and right.... I just can't figure out why it's getting shifted like this.
Excel 2010. How/where to define userform object?
Button on worksheet has following code:
Sub Button1_Click()
Load UserForm1
UserForm1.Show
End Sub
Execution of this code generates following error:
Run time error 424
Object required
How/where to define userform object?
I need a formula or array to perform the following in Excel 2010:
I have 2 worksheets "Master Report" and "Free text comments". The master report will be shaed with individuals but the Free Text comments worksheet will not.
sample feedback report.xlsx
In cells D2 downwards on the "Master Report" I want to show the individual feedback comments from the "Free Text comments" worksheet that have been made by other people for the individual named in cell A2 on the "Master Report" worksheet. So on James' master report it would show the 8 individual comments listed for him on the Free text comments" worksheet.
I have searched the internet and tried various combos of lookups, IFs, index and match but have not come across the right formula yet.
I have Columns A to C which are hidden. I have tried to unhide these bey selecting the entire worksheet but to no avail. I am using Office 2010.
View 4 Replies View RelatedI have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!
I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.
=Results!#REF!
=INDIRECT("Results!$B3")
=indirect (cell("results",B3)
i found this code...
Code:
Sub Button1_Click()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim SNfound As String
'Your sqlserver 2008 connection string
Const stADO As String = "Provider=SQLOLEDB.1;" & _
"" & _
[code].....
but i dont see where to put in the Database object...
The Database it needs to connect to in SQl is called
The Server name is SQLSRV when you expand databases the database is called SWHSystem the Table is called dbo.Credential and from that i need to get SELECT All from the Name and CardNumber from dbo.Credential and put that in a New Sheet titled Personal
using Excel 2010 connecting to SQL 2008
I am trying to add the sheet name to the center footer for each worksheet in a workbook. The workbook has about 80 sheets and it is cumbersome to do this manually. I am using Excel 2010.
I have tried to record a macro capturing what I do manually, but when I run the macro on another sheet, it does not add the sheet name to the center footer.
I have tried searching for a macro online and the ones I have found just crash excel.
I know I am probably missing something obvious in my macro code.
I know there are many ways to create an "All Data" worksheet. Copy & Paste is the most obvious or pasting named ranges into the new worksheet. I have a workbook with 48 tabs with up to 1000 rows of data per sheet. I need to merge each tab into one main "All Data" worksheet.
How to combine these 48 sheets in an easier way than the two options I already know (Copy/Past or Paste Named Range). Any Add-In's to Excel that can possibly do this? I am using Excel 2010.
How to make this "All Data" worksheet combining data from each sheet of the 48 tabs would be most useful.