Excel 2010 :: Lines Count - How To Expand Amount Of Columns In One Spreadsheet
Dec 27, 2011
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.
View 4 Replies
ADVERTISEMENT
Jun 16, 2012
I have to construct a financial model for Senior Executives to show year to date spent amounts. I have my worksheet as follows:-
Cell A2,A3,A4.. to A100 has - Account Numbers (Ex. A/c. 4100..)
Cell B1, C1, D1.....has Jan2011,Feb2011,Mar2011.......and so on till Dec2011.
Cell B2 onwards, down and to right, all spent amounts by month
What I need is a formula to get year to date number, which will change to Executives requirement.
Cell-ABCDE
1Jan2011Feb2011Mar2011Apr2011
24100100100100100
34101200200200200
44102300300300300
Year to dateMar 2011( Months will be changed)
Account 4101( Accounts will be changed)
Amount should be 600 What Formula ?
My excel version is 2010.
View 7 Replies
View Related
Mar 27, 2013
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 Related
Feb 20, 2014
Using Excel 2010.
I have data in excel which looks like this:
Column 1 has 1200-1209,1300-1350,1523-1563
Column 2 has 1400-1409,1600-1650,1823-1863
I would like to take the range of e.g. 1200-1209 and have excel put 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 into separate adjacent cells for me. And be able to do this for each column/cell of data I have like this.
Column 1 1200
Column 2 1201
Column 3 1202
Like that only. Is it possible?How?
View 4 Replies
View Related
May 27, 2014
I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:
A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2
I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:
Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)
Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0
Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0
and so on....
View 9 Replies
View Related
Mar 26, 2014
The situation is that I have 3 interest income from 3 different companies. Column B acts as a helper column to determine if a partner receives just one interest income or receives multiple interest income. If they receive multiple interest income, I want column B to display a 1 and if not make it blank. If you notice partners 5, 9, and 16 contain two or more interest incomes (Columns C - E). All the cells are have either an amount (positive or negative) or a zero. There are no blank cells. Columns (F - H) contains the interest income amount if only that partner receives just one interest income. So for those partners they should not display any of their amounts. The reason is because I am using a word template and currently have to use multiple templates ( 3 in total) to display correctly for each partner. I got this to work with a word macro that will collapse all non blank columns in a particular table. This might be on a ongoing project for me as I do have more logic to solve.
Excel 2010
A
B
C
[Code]....
View 2 Replies
View Related
Nov 18, 2011
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 Replies
View Related
Dec 12, 2011
I am trying to create a chart that would show all of the Amount values for each Size value that is in the example data below. The Size column has fixed values. In my example the values are: .5, 1, 2, 5, 8, 13 and 21. The Amount column data is variable. Any of the static Size values can be associated with many Amount values. I was attempting to have the static Size values on the X (horizontal) axis and the Amount values on the Y (vertical) axis. I would like to have all of the Amount values for each Size value be shown directly above the corresponding size value, in one vertical line. See example below.
I an creating this type of chart in Excel 2010.
Note, I am still new to this forum and don't know how to post pictures or Excel data into my posts. I am also using dots (periods) to provide separation in my examples.
Amount...Size
58.5.......21
1........... 0.5
2........... 1
1.5.........1
27.5.......13
12.5.......5
2............1
3.5.........2
17.5.......8
4........... 2
48..........21
0.5.........0.5
15..........5
11..........2
Chart that I am trying to create. The chart example is not to scale.
...........60.|
...............|..........................................................................*(21,58.5)
Amount.50.|
...............|..........................................................................*(21,48)
...........40.|
[Code] ........
View 2 Replies
View Related
Mar 13, 2013
I have a 5K Excel 2010 workbook with VBA code that until yesterday, took about 30 sec. to run.
Today, after installation of WIndows7 Home Edition automatic updates, the running time has increased to about 4 min.
View 6 Replies
View Related
Feb 23, 2012
I 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 Related
Apr 29, 2014
i 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].....
View 2 Replies
View Related
Nov 16, 2012
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 Related
Nov 30, 2011
I'm trying to create a pivot in 2010, which gathers a cumulative amount from data sources within two different worksheets.
View 2 Replies
View Related
Nov 21, 2013
I've created a spreadsheet in which I want a blank row added when a previous row is filled in any of the boxes. I've used VBA code to insert a row but it just inserts a row in which all the cells are unmerged. I want a blank row the same as the ones we're filling so the document auto expands and there's always a blank row available to fill.
View 5 Replies
View Related
Feb 16, 2012
I 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 Related
Sep 9, 2013
Im using excel 2010. Im looking to have a map of the world in excel. I work in sales with 2 other people and we wish to divide the world up by countries.
It would be nice to have the names in a list and colours represent the countries.
View 2 Replies
View Related
Feb 3, 2012
Using Excel 2007.
I have some connector lines that cross over tables in Columns G:K that are connecting tables in $D to tables in $M
How can I make the part of the connector line in $G:$K invisible?
something like:
Code:
Sub FormatConnector
Dim oConnector as Shape
With oConnector
If .Connector Or .Type = msoLine
If .Connector In Columns($G:$K) Then
.Connector.Segment.Visible = False
End If
End If
End with
End sub
View 1 Replies
View Related
Sep 18, 2006
I built a web form interface which I use to adjust orders on a web-based system our company uses.
The code I have looks at a sheet, checks if the line needs adjusted, makes adjustments as needed, and then clicks a box for lines needing ordered.
Here is the code I use:
Sub Adjustorder()
Application.ScreenUpdating = False
Dim ie As Object
t = ThisWorkbook.Sheets("Adjust").Range("C3").Value 'checks for valid orders to adjust
If t = 0 Then MsgBox ("No Skus to add"): Exit Sub
Set ie = CreateObject("InternetExplorer.Application")
With ie
This all works fine, but the amount of lines can be anywhere from 1 to 11,000. The fewer the amount of lines needed to loop through, the faster it does each line. With 11,000 lines it took nearly 1 second per line. With 200 lines it took 2 seconds for all of them.
View 9 Replies
View Related
Mar 2, 2013
I'm have Excel 2010 and Windows XP. Each week I get 3 spreadsheets and each has data unique to it. Each spreadsheet has a week number column which is common to all three. I want to combine the three worksheets into one and make create several dynamic charts for management. How should I organize a large spreadsheet? In addition to my week number I have a host of other date fields. Some of the data I get is (1) vehicle VIN numbers (2) City/State/Country (3a) I break up the VIN to give me vehicle type (3b) year of assembly (3c) car type (3d) number of doors (4) mileage (5) complaint (7) defect code etc. etc. What are the do's and don't when setting up a large spreadsheet? I have data by week which goes back to 2006 and so my date fields go top-down. I inherited the three spreadsheets and would like everything under one roof, so to speak. One spreadsheet which I can make my charts.
View 1 Replies
View Related
May 23, 2013
I have a system running Windows 7 Professional (32 bit) and MS Office 2010.
In Excel 2010, I have a spreadsheet that contains several hundred rows of data. When I cut and paste a section of data, Excel jumps to the top of the spreadsheet. This does not happen when I copy/paste, just cut/paste.
View 1 Replies
View Related
Oct 14, 2013
I just want to add it to my excel 2010. How can I add a date picker in my spreadsheet, when the cell contains a date format????
View 14 Replies
View Related
Jan 16, 2014
Open a brand new spreadsheet whether the format is set to "General" or "Text" it will not let me enter numbers. For example, when I enter 2007 in the cell it displays 20 07 not really that big of space between the two zeros - but still a space. I can't format this no matter what I do.
View 1 Replies
View Related
Nov 13, 2012
In Excel 2010, spreadsheet with part # in column A and customer code in column B. Many part numbers have more than one customer buying the part.
I need to rearrange the information in the example below, where there are two part numbers:
Part Cust
334 CCC
334 CHD
334 CIL
401 CIL
Looks like the layout below, where there are still 2 columns, part # and customer, but with the customer(s) concantenated in column B:
Part Cust
334 CCC, CHD, CIL
401 CIL
Can this be done with any wizard type tool (pivot table, etc)? I am sure it could be done with VBA.
View 5 Replies
View Related
Jul 14, 2014
We have a spreadsheet on a server.Without using code is there a way to show who saved the workbook last?
using 2010
View 1 Replies
View Related
Oct 17, 2013
Using MS Excel 2010.
As I understand it hyperlinks set in a spreadsheet should work regardless of where you save and/or copy the spreadsheet i.e. as long as the directory location of the location / file the hyperlink is referencing doesn't change (notwithstanding, the user must have appropriate access rights to the relevant directory locations)
However, the situation I have is the spreadsheet was generated and saved to directory location A (intention being this would be the 'master' yearly template) and the hyperlinks set, all of which work when you open the spreadsheet in this directory location. But when I copy the spreadsheet to directory location B, which is intended to be the yearly 'register' location for the spreadsheet, none of the hyperlinks work? When I hold my cursor over a link in the copied spreadsheet, in directory B, I do not get a 'pop-up' path like I do in directory A and when I click a link I get an error message:
Microsoft Excel The address of this site is not valid. Check the address and try again.
As stated above the documents the hyperlinks relate to have not moved, they remain in the same template directory (i.e. the link path has not altered).
View 1 Replies
View Related
Jan 31, 2014
I work with Excel 2010 and have a very large spreadsheet with data that I need to manipulate in several different ways. I have been filtering and then cutting and pasting but this is very time consuming . Is there a way to extract specific data from the spreadsheet and transfer it to different worksheets? I don't really know how to use macros.
[URL] .....
View 14 Replies
View Related
Jun 2, 2014
I have been tasked with streamlining a process to collect data from a specific online website (Web of Science) and import it into an Excel 2010 spreadsheet.
Currently they are going to the website, entering a short number of search parameters and then manually recording the pertinent data from the webpage. They would like to be able to enter a keyword in Excel (which acts as the search item) which then automatically does the rest of the process and provides them with a spreadsheet of the required data.
Is this possible? Perhaps by using Visual Basic code within Excel? I also saw a method that employed SharePoint Server 2010.
View 3 Replies
View Related
Aug 22, 2012
We have a spreadsheet on a server location with macros. Different users will access the spreadsheet from one pc on different shifts. Is there a way to set the area as a Trust Location for all users on the PC or enable the macros in the spreadsheet to execute for all users without having to set up the spreadsheet trust location or macros for each user? I would like to have the spreadsheet accessable to any user on the PC without setting up individual access. The users can change for shift to shift.
View 2 Replies
View Related
Oct 10, 2012
Im trying to enter a formula for volatility in my excel 2010 spread sheet
my hig low close is listed verticaly in A,B,C
The formula for D1 is supposedly “={LN((A1)/(B1))}^2”
The formula for E1 is supposedly =SQRT(SUM(D1:D10)/10)*SQRT(252)*100
I put this in D1 and it is giving me an error
View 3 Replies
View Related
Jan 17, 2013
We updated excel from 2003 -> 2010 and this stopped working.
We have a macro which takes pictures from a folder and pastes those pictures in the excel -sheet.
At 2003 the pictures were correctly pasted to their positions, but at 2010 the pictures appear in somewhere at the same sheet. (not at the right places, which should be at column "o" at the same row.)
Macro (working correctly at 2003) :
Sub Kuvan_piirto(ic As Integer)
On Error GoTo loppu
SourceFile = Enari
DestinationFile = "Live1.JPG" ' Define target file name.
FileCopy SourceFile, DestinationFile
ActiveSheet.Pictures.Insert("Live1.JPG").Select
[Code] ..........
What should I change for 2010?
View 1 Replies
View Related
