Deleting "hidden" charts on a worksheet
I've been playing around w/ a worksheet that contains data and charts,
inserting and removing multiple rows at a time. Unfortunately, I did
not change the properties of the charts and they were all set to "Move
and Size w/ Cells". When I deleted a series of rows, these charts
disappeared and left a line across the spreadsheet where they used to
be. I've tried everything to get rid of them (because they are now
calculating incorrectly and giving me error messages), including
deleting the rows where they appear, trying to select them on the
sheet, but nothing w...Prepending values in a column with "\"
I would like to prepend all text values in a column with "\". What is the
easiest way to do this if there are thousands of rows?
Assuming column A has the text values.
In B1 enter ="\" & A1
Double-click on fill handle of B1 to copy down.
Copy then paste special(in place)>values>OK>Esc.
Delete original column A if desired.
Or you could use a macro to do in place with no formulas.
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," &...Pivot chart and Total row
I have a Pivot table and attached Pivot chart (all good so far). However,
there are three columns (drop column details here) which are the two columns
of data and a grand total column (showing the average of my two data columns)
When I turn it into a pivot chart it does not display the grand total column
as well (is there a way to do this). I have tried to go into the chart
options but these are locked.
I can copy and paste the results but this is a bit clumsy.
Any suggestions - any help greatly appreciated
PS using excel 2003
...Surpressing plot of blank cells
I'm creating a line chart plot using Excel 2007. The data range for plot
lines contains some blank cells. These blank cells get plotted as zero
How do I change this behavior to plot only points that have values and to
leave the blank cells unplotted?
I appreciate your help, -John
If they are truly blank then check the plot empty cells setting via.
Select Data > Hidden and Empty cells.
If the Cells in fact contain formula then use NA() instead of "". This will
suppress the data marker but will not break the line.
Alternatively use Autofilter to hide un...Changing cell values through form
I'm not that good with access so I decided to look for help here.
I have a table filled with drivers.
Starting number, Driver name, and Number of laps are the columns.
My Idea is to make a form that will list all the drivers, and
add a value of +1 into the "Number of laps" cell to that driver
when double clicked.
That would be a sort of Lap counter form...
Every suggestion is welcome.
::: www.ktvj-online.com - Motocros Portal :::
::: www.forum.ktvj-online.com - Forum :::
...Re : Excel Range of Values Amidst Characteristic Transitions
Re : Excel Range of Values Amidst Characteristic Transitions
Enter an Excel worksheet ; now that the tabulation is prepared for
(preferential) presentation, Column A is shown as follows :-
03 <A Blank Row>
05 <A Blank Row>
06 <A Blank Row>
08 <A Blank Row>
11 <A Blank Row>
14 <A Blank Row>
15 <A Blank Row>
16 <A Blank Row>
18 <A Blank Row>
20 <A Blank Row>
22 <A Blank Row>
24 <A Blank Row>
25 ...2nd try, parameter values
I'm having problems posting... sorry if this comes up twice.
I have parameter values set to make things easier for the users...
The prompt pulls Last Name or Emp #. If the Emp# is not available, is
there a way to search by last name, including suffixes, but only
entering the last name? or even just the first letter of the last
On Thu, 19 Jul 2007 09:31:24 -0700, firstname.lastname@example.org wrote:
>I'm having problems posting... sorry if this comes up twice.
>I have parameter values set to make things easier for the users...
>The prompt pulls Last Name or...How do I delete chart superimposed on Excel document?
Created Excel document. Print preview did not show gridlines. In attempting
to get gridlines, somehow got into charts. A chart was created and is
superimposed on original document. How do I get rid of the chart? How do I
get gridlines on to original document?
Right-click on the chart and Edit>Clear>All
To print gridlines.................File>Page Setup>Sheet
Check "gridlines" and uncheck "draft quality".
Gord Dibben MS Excel MVP
On Fri, 4 Aug 2006 14:27:01 -0700, Oz Osborn <Oz
>Created Excel docume...VBA to insert .xlborder if cell value not equal to previous cell
I've got a worksheet and I'm wondering whether it is possible to
insert a line when a value in Column A, B, C & D does not equal the
values in the row above or below it.
I've currently got a formula in Column A that reads....
=IF(AND(B3=B2,C3=C2,D3=D2,E3=E2),"","IL") and a conditional format
that if the cell value is equal to "IL" then put a border. Wondering
if there is a better way to do this via VBA or is that the better way?
In your conditional formatting formula, instead of
(which I assume is what you've...Using commas in "Value List" list boxes
I am trying to add a column that contains a comma to a 'value list' list box.
I have a patient name (Last, first MI) column and a patient ID column (among
others). I have my Row Source Type set to Value List and the number of
columns correct, column widths etc. all correct, but when I try and do this:
lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId"
The help documentation shows that the delimeter should be the ; not comma's,
however instead of getting the name all in one column, it is being placed in
two separate columns (because of the comma... it is...Chart: range info from seriescollection?
I would like to fill a combobox (Rowsource) with data that are used in for
instance the XValues or Values of a Seriescollection of a graph.
"ActiveChart.SeriesCollection(1).Values" does not give the right type (I was
hoping for a string type of info like "Sheet1.Range(a1:a10)").
How to get a Range to be used as Rowsource for my combobox???
Thanks for your suggestions,
The Values and XValues will return an array of values. To get the range
address like this, you will need to parse the series formula. Or you could
use John Walkenbach's class module for extractin...Is it possible to save an excel chart as .emf?
I would like to save an excel chart as .emf instead of .jpeg or .gif using
the script below. Is this possible?
Sub SaveChartAsGIF ()
Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif"
ActiveChart.Export FileName:=Fname, FilterName:="GIF"
...Step change charts
I am trying to create a bar charts by year but only want
to show increases and decreases through the middle years
as step changes i.e.
How can I do this? What I should see is the first vertical
bar being 100 then from the 100 level an increase of 10 to
get to 110 then a decrease bar of -30 (from the 110 level)
to get to 80 etc etc.
For a tutorial, see the Excel | Charts | Waterfall Chart page of my web
Tushar Mehta, MS MVP -- Excel
Excel, PowerPoint, and VBA add-ins, tutorials
Cus...Excel Bar Chart with many elements Example
I am working with a survey database where one field represents
one question in the Survey. Some questions allow the user to
enter multiple answers. This data isn't normalized but I have t
work with what I've got. Anyway I tried to create a chart in Acces
with the wizard but got the error: "Cannot have more than
elements in the chart". Anyway I attempted to recreate the char
in Excel but my chart looks crammed and canno
print entirely within one page. Does anyone know how or have a
example of how to create a bar chart with many elements in a clea
and consice manner
T...stop opening a blank workbook at start up
how do I stop excel from opening a new workbook everytime I start up the
Ad a shortcut to the Excel.Exe file and append the properties of the
shortcut with /e
Shotcut will look like this:
"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /e
"km" <email@example.com> wrote in message
> how do I stop excel from opening a new workbook everytime I start up the
...Can no longer cut/paste ranges in chart "edit data" fields?
In Excel 2007 I can no longer cut/paste cell ranges into the x and y datasets
using the edit data tool. Is there a way to turn this back on?
Also the chart range resets itself to the current length of the data,
however, in many instances I want to set an exact future range. Is there a
way to have excel not reset the range to what it "thinks" I want.
> In Excel 2007 I can no longer cut/paste cell ranges into the x and y
> using the edit data tool. Is there a way to turn this back on?
I have no problem doing what you can't. Could you provide more det...how to down load more than 1 year statements
I like to download more than one year of statment from my
brokerage. But it only goes back to 1 year.
I want to download statement from 1-1-2003 to 12-31-2003.
but since I am downloading in March 2004 it only goes
back to march 2003.
I had money 2001 it didn't have the option to download
more than one year of statement. I got mony 2004 but same
thing it only goes back to March 2003.
I have noticed that it can arrange reports from 1-1-2003
to 12-31-2003. But still missing the actual data from 1-1-
2003 to March 2003.
Please advise how I can achieve that goal.
Thanking in anticipa...Changing bar graph colours according to value
Is there any way to have the colours of the bars in a bar graph change
according to value?
Have a look at Jon's example.
> Is there any way to have the colours of the bars in a bar graph change
> according to value?
Andy Pope, Microsoft MVP - Excel
...Need to format text in header, but value is generated using VBA
This is a multi-part message in MIME format.
I wanted to reference a cell value in my spreadsheet header and found =
out how to do that using this function:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim WS As Worksheet
For Each WS In Worksheets
ActiveSheet.PageSetup.RightHeader =3D _
Format(Worksheets("Time Period Info").Range("B3").Value)
However, this leaves my text for ...how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a
running total of my supplies ie... I have 2 pens, remove one and receive 2.
answer in the cell becomes 3 pens then if I zero out the received cell the
inventory cell still remembers that I have 3 pens not 4 pens (because I
received 2 more pens) and visa versa when I zero out the received cell it
remembers I have 3 pens not 1 pen(because I removed 1 pen)
I believe the method you suggest is flawed. You have no audit trail. If the
number on the sheet doesn't match your actual inventory, how will you figure
out...can not see chart
I created a simple chart. For some odd reason the chart is all gray. I can
only see the chart if i select the chart object. Please help! Thank you!
Via the menus Tools > Options. On the View tab select Obejcts Show All.
> I created a simple chart. For some odd reason the chart is all gray. I can
> only see the chart if i select the chart object. Please help! Thank you!
Andy Pope, Microsoft MVP - Excel
...on server reboot multiple emails get returned as undeliverable
I have an SBS 2003 server running exchange, all latest service packs etc.
We have hit a problem a couple of times when on reboot of the server end
users get emails returned as undeliverable as they have exceeded the
It does not affect all outgoing messages, and the destinations are valid. It
is as though the SMTP gets stuck on some messages, and only notices on
Frank <firstname.lastname@example.org> wrote:
> I have an SBS 2003 server running exchange, all latest service packs
> We have hit a problem a couple of times ...Print notes on purchase order blank report
How can I print the notes from purchase order header on the purchase order
blank report? thank you in advance.
You can modify the PO in Report Writer. Link the Records Notes Master
to the Purchase Order HEader table and include the notes table in the
report. Then the notes text field will be available to be displayed in
On Nov 17, 6:38 am, "Jack Tundra" <jacktun...@hotmail.com> wrote:
> How can I print the notes from purchase order header on the purchase order
> blank report? thank you in advance.
I am sorry but I cannot find th...How to avoid when linked source data is changed, chart turns blan.
When I created new charts in Excel 2003, each previous chart became blank.
Therefor I saved the file unter a new name and copied the previous chart to
the new file. This created ofcourse links to the other files.
Now I want to change the source file, but when I do that the chart becomes
blank as well.
Anybody who had this problem before and found out how to solve it ?
...chart type keeps changing
My chart is "embedded" in my worksheet. The worksheet has months across the
top in one row and net income per share in a row below it for each
corresponding month. The x-axis is pulling data for the month and year eg.
Jan-04...Feb-06. The y-axis is pulling data from a different row for the net
income for each month.
The chart has been maintained since Jan-04. It is a standard clustered
w/ a 3-D visual effect. I am trying to enter data for March '06 in data
source, series one. I am updating the series 1 with the new column address
that represents the new month eg...