First, all charts are complete and visible in Excel, the problem is Printing. This is very hit-and-miss as to how many spanned pages can be printed.
Sometimes I can span over 4 pages, other times I can't do 1.5
The only think I can think of is some kind of memory issue - either Excel, Printer, or Windows. However, that is not obvious either.
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
I have this question for indentifying the last row (number) for each and every page to be printed.
As I am using a loop to determine the last row of the table in each print page, I am only able to do in "hard coded". And with this "hard-coded" row number, I can set bordering to the table so that table will not be printed without any border (for in between pages if table is more than a page)
But the problem is that I had discovered that once any other user start using wrap text, or even change the row height, the macro will still captures the pre-determined no. of rows to do the bordering (as shown in my code).
In my, I had restricted the zoom size so that the quotation will always be the same format. Hence, based on this control zoom, the printable page changes if the row height changes too.
With destSh.PageSetup
.PrintArea = "$A1:$H" & LastR
.PrintTitleRows = "$1:$21"
.Zoom = 62
' .FitToPagesWide = 1
' .FitToPagesTall = 8
.PrintErrors = xlPrintErrorsDisplayed
.RightFooter = "&8Last Saved : " & _
Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"yyyy-mmm-dd hh:mm:ss")
.CenterFooter = "Page &P of &N"...................
Is there a way to 'lock' a page from being changed once it has been printed? It can be printed multiple times but want it to not be able to be edited once it has been printed. Thinking I could have a control button on the page maybe and once it has been checked ok to print then click the button and it saves it as is and uneditable?
View 3 Replies View RelatedI have spent some time looking through the forum and even looked at Microsoft Excel help file and cannot seem to get exactly what I need. I have worksheets that I found the code on this site that I can put page breaks every so many rows. I have it set for 26 so it will break for every 25 counting the header. Here is the
Sub PageBreaks()
For I = 27 To 760 Step 25
ActiveSheet.HPageBreaks.Add Before:= Range("A" & I)
Next I
End Sub
The problem I am having is when I print the first page it has the headers in them, for example.
LastName, FirstName, StuID, GrdLvl
How can I get a row placed right after each page break with with the column headers so each sheet I print will have the headers with the 25 rows of informatio. But then not be there after I print so If I need to sort I won't have a problem with all the headers.
Is there a way I can display the total of a column on each printed page?
I have a table which is about 6 columns long but is hundreds of rows down. I would like the total of one of these colums to be displayed on every printed page. Is this possible?
Cell 1A = Lot# Cell 1B = Last Name Cell 1C = First Name Cell 1D = Phone. I need Row1 to be repeated on each page. The list has about 200 names. We live in a senior community so the list changes quite often.
View 3 Replies View RelatedUsing Excel 2003 I have had this problem ocassionally, and I can't figure out what causes it or how to fix it. In a workbook of several excel worksheets I will ask to print a mulitple page worksheet and only 2/3 of every page prints. This also happens if I print to an Adobe acrobat file, and typically happens when I am printing to pdf files. The selection is correct; there are no page breaks . . . it is just some sort of glitch. The only way I get rid of it is to close down and reboot .
View 8 Replies View Relatedis it possible to make row 1 the (headingline) top of every printable page? office excel 2007 by the way
View 3 Replies View RelatedI have a excel file to print label on a label printer.I've added additional file in this message.I'm printing different numbers of labels with this file.for example, if I print 10 pieces of labels, I want to write 1/10 , 2/10 , 3/10 etc. on each label.I finded a vb code to print this work but not exactly the way I want.I would ask one of excel guru's to look at my file.
View 8 Replies View RelatedI have a selected area which I print as labels.
I want to have a number on this label preferably counting back from the quantity of print.
So if I select 300 copies to print, the first label shows somewhere 300, the 2nd label 299, the third 298 and so on.
I use a button and macro for printing selected labels, so perhaps the reverse printing could be added there if only counting up is available.
Also to make it easier, a cell on the sheet can contain the number to count down from (this is preferred on thinking about it) So I could then print 20 labels from 300 to 280 say.
Trying to figure out the width of a single line of printed characters for Landscape v. Portrait (obviously the margins would change this).
View 3 Replies View RelatedI've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
I work in a call centre and I record my daily figures. My target is 82%, and I would like a bar chart in Excel 2003 to colour the chart according to wether I hit target or not. If my daily figure >=82% the bar should be green.
View 9 Replies View RelatedThe following code works fine except when the sheet1 gets printed then its blank but in actual the sheet1 has got some information in it. SO why its printing a blank page.
[Code] .....
I have 3 test scores for each student. Currently, I have 3 students.
For example :
John 95 90 85
Cindy 50 60 100
Dan 87 86 90
I have a chart that that plots 3 lines, one for each student.
However, I want the chart to dynamically update if there's another student. In other words, I would like the chart to add another series while the legend updates to include another student.
I am trying to do this in Excel 2003.
I am using the the stock chart and I would like there to be a horizontal line across the chart with a y-axis of the opening price. In the end I want this to be in VBA but how to do this manually I should be able to put it in loop of data that will produce many charts with a horizontal line starting at the day opening.
I have tried to add a new series to the chart but I'm using the Candle stick stock chart and the new series comes out as a vertical not horizontal line. I read about using a secondary horizontal axis but all the tutorials I find are in Excel 2003 and they donlt line up well with Excel 2007.
I found this link on charts with horizontal lines but again they are in 2003 and I'm using a candle stick stock chart not a scatter chart. [URL]
I have some data that I'm plotting on a bar chart and I'm trying to "HIDE" the columns with zero or null values. Basically, if the column is blank, I don't want a "gap" on the chart. I'm not getting this to work.
View 6 Replies View RelatedI'm using Windows XP with MS Excel 2003. I have a pivot table representing a survey. Let's say I've built the survey outside of excel and I've imported the response data into Excel. One of the questions in the survey is "ratings" and the possible valid responses for it is: "Excellent", "Good", or "Poor". In my data set in excel let's say I have 10 responses or rows and all the responses for the question on ratings are either "Excellent" or "Good". (There are no rows with a "Poor" value in the ratings column).
For example, let's say out of the 10 responses, 6 are "Excellent" and 4 are "Good". As such my Pivot chart shows two bars: one for the number of respones with "Excellent" (10) and another bar for the number of responses with "Good" (4). My delima is how to show a third bar showing "Poor" with a zero as the number of responses.
I have a master list of Players on one sheet B2:B72 and in E2:E72 is a column called Playing and in it is "y" or "n". I need to make a list of the Players that are Playing on another sheet without blank rows (I can do it but it leaves blank rows for the Players that have a "n" from column B. I am using 2003
Here is the formula I am using now: =IF(Players!$E3="y",Players!$B3, " ") but I get blank rows for the player that are not playing.
I am trying to perform a count against two ranges of data.Both ranges contain contain values (dates in my case).I am trying to perform a count where cells in column A have data but cells in column B do not.
Is there a way this can be done in Excel 2003? I have tried numerous COUNT and SUMPRODUCT queries but have not found a way to get this to work.
My office recently upgraded to Office 2010 and we would like (in the accounting department that I work in) to change the default number formating in a blank sheet to Number, 0 Decimals, using seperators, from the current default of general format number.
I have looked for the Book.xltx file to replace but can't see it any where.
I have a 97-2003 spreadsheet with approximately 22,000 lines of seperate companies. I need to know how to insert a blank line between the company names. I have been using the "Right Click - Insert" method but soon realized there must be 2000 different companies. The Company Name column is A.
View 7 Replies View RelatedI have a pivot table based on a large dataset within Excel itself. The dataset looks as follows:
View 9 Replies View RelatedWe have a schedule that creates an Excel file which separates each order by blank rows. I need each order to be separated by a page break, so each order prints on a separate sheet.
Each order entry is 1 or 2 rows
Using Excel 2010/2013 although the file produced is an xls file.
Columns A to J are populated
Rows 1 & 2 are the header
I have tried a macro that involves me doing a countif and counting entries in the row, and if they add to 10 it inserts a page break, but its bit long winded and only seemed to work once.
how to write a formula that will leave a cell blank if nothing is entered. I do not want it to show a 0 unless the cell entered is a 0.
In cell G16 - I am adding U46 and U58 together.
I do not have a problem when it is 1 cell - my formula works fine. When I have 2 cells added together, the formula does not work.
We make many graphs using XYscatter charts with lots of data points using Excel 2003 with the horizontal scale properly scaled as frequency. I have been asked to label that axis in some way as period (=1/frequency) without changing the scaling for the data plot. Is there a suitable way to do this? It would be OK to just change the axis numbers to 1/frequency computed from them automatically. Is Excel 2010 any easier for this?
View 9 Replies View RelatedI am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))