Copy filtered data (Values only)
The following code copies filtered data:
Set Rng = ActiveSheet.AutoFilter.Range
but copies values and formulae. (e.g. .PasteSpecial xlPasteValues)
How do I modify to the copy only the values
Set Rng = ActiveSheet.AutoFilter.Range
Microsoft MVP Excel
"Steve" <Steve@discussions.microsoft.com> wrote in message
news:5E54D33F-2...Is it possible to plot this kind of data in a (line) chart w/ preferred axis? How?
How would I plot this data in a line chart?
(MS Excel 03)
(Samples below should be viewed in non-proportional font or formatting
will not be clear)
Raw data (below):
A B C D E
0 @ 1000 0 @ 1000 0 @ 1060 0 @ 865 0 @ 1200
10 @ 1700 10 @ 1700 2 @ 1340 2 @ 1325 2 @ 1460
na na 17 @ 2400 11 @ 2400 12 @ 2200
26 @ 5000 26 @ 5000 24 @ 4800 18 @ 4200 20 @ 5000
The catch is I need to have the 2nd # be the X axis (0...6000).
I can put the data in the spreadsheet something like this and tell it
to ignore empty values (...Compare Worksheets #2
is there any way to compare two worksheets? The limitations in the
1. The cells in some columns are merged.
2. However, all the data, whether merged or not, the formated remains
the same. I meant if a1 and a2 are merged in sheet A, the same a1 and
a2 are merged in Sheet B.
Please help if there is any way to compare the worksheet.
Also am not good at script. Please specify if there is any method for
Your quick response is appreciated.
praveen_khm'...moving between worksheets
I can't recall how to move between worksheets using a
keyboard shortcut. I'm wasting my time using the mouse
to move between worksheets, and I'm hoping somebody out
there can remind me.
Use Ctrl+PageUp and Ctrl+PageDown. .
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"T" <firstname.lastname@example.org> wrote in message
> I can't recall how to move between worksheets using a
> keyboard shortcut. I'm wasting my time using the mou...Excel worksheets #5
is there a max number of worksheets allowed in one workbook in excel ??
The restriction is caused only by the amount of RAM on your PC; however, I
would never recommend more than 100 sheets or so...
"Anthony" <Anthony@discussions.microsoft.com> wrote in message
> is there a max number of worksheets allowed in one workbook in excel ??
I was thinking around 100 - so thats fine,
"Anne Tro...Multi data charts
I want to build a chart which shows the year on the horizontal axis and the
totals on the vertical axis. The tough part is there are six diiferent data
series for each year that when summed give the total for the year. I want to
show one bar for each year with each individual data point stacked on top of
each other to show the total bar size. I've done it before with 2 series but
can't get it to work with six. Help!
are you using the stacked column chart?
"thomas donino" wrote:
> I want to build a chart which shows the year on the horizontal axis and the
> tota...Summary of Data from a Range
I have a range of data, which is a weekly sports picking competition at work.
Basically on the left it has the event, and along the top it has my work
The sports picks are in the range and are in the form of validation lists.
What i would like to do is create an automated summary of who picked what at
the close of each week, which i can email out to the guys in the competiton -
i.e: Event 1: New York Mets: persons a, b, c, d and l , Cleveland: e, f, g,
h, i, j and m
I have 8 events per week, with a number of options per event, thus it is not
easy or quick to just ma...Workbook & worksheet name formulas
Is there a formula that obtains the workbook name that I can put in cel
Is there a formula that obtains the worksheet name that I can put i
Thanks so very much. mikebur
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=48298
Workbook need to be saved
=SUBSTITUTE(MID(CELL("filename",B1),FIND("[",CELL("filename",B1))+1,255),"]"&MID(CEL...Adding shapes to worksheet
I have the following code
Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
' determine positions
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - ...Lowest 5 numbers in data base column
I am working with a data base where I compute a golf handicap based o
each individual's lowest 5 scores from the last eight rounds played.
The data are arranged in columns showing date played and score posted.
Not all dates have a score for each person. Can someone provide
method for determining handicap based only on the lowest 5 rounds
Message posted from http://www.ExcelForum.com
This formula adds up the lowest 5 scores
should get you started
... looking out across Poole Harbour to the Purbecks
(remove nothere from the...Data retrieval in a Matrix
As usual, I really want to thank everyone who did help on my VB code. I
today come with new problems that did arise while developing and I have
a few brand new questions unfortunately ...
I need to create a matrix of prices on Sheets("Matrix"). In other
words, I want to input a Price at the intersection of a product
(products are on lines) for a given month (months are in columns). It
will be more or less like a Pivot Table at the exception I will
interpolate the missing data for a given month.
References of the products are thus sorted on lines, dates on columns
of the Matrix Shee...Data Query
I would like to create a query. .. ie. NFL sched for 2005. on the 1st sheet
that pulls individual team info from the 2nd sheet. So I will input a team,
and have the query search through a range located in two columns on the 2nd
sheet. The info in the first column, 2nd sheet, is date (not necessarily in
order). the second will contain info re events on that date. One of two
column headings on 2nd sheet will allow me to specify the query subject
(ie.ny giants) as the range identifier.. So really, my problem is, setting
up the correct query, (dependant range) - then have it displayed in
...how to globally change souce data on plot
I have a x-y scatter chart with about 40 individual lines originating from
individual spreadsheet tab for each line. I need to clone this chart
multiple times, changing only the column references for the x and y
parameters. To accomplish this I currently go to "chart," "source data" and
manually change the x and y references for each line. Is there a way to
globally change the cell references for all line on the chart?
This web page describes a technique to change series formulas en masse:
It provides a link t...I want to show chart data in order of bars, not row names
Operating System: Mac OS X 10.6 (Snow Leopard)
I am trying to show a chart that has about 20 values, and I have sorted the source data so that I have the highest value items at the top and the lowest value items at the bottom. For example, if rows were Apple, Banana, Guava, Orange, and Yucca, I have sorted the data in order of the greatest value items (20 Guavas, 10 Oranges, 5 yucca, 2 bananas, and 1 apple). Yet the chart (a bar chart) only seems to let me display in either ascending or descending value by food name (either Apple to Yucca or Yucca to Apple...Consolidating Personal Folders
I have somehow opened up 4 personal folders. How do I consolidate all of
them into one personal folder and merge all the information. Next, how do I
delete the other personal folders? I've read through a couple of books but I
can't find the information. Any help would be very much appreciated
Move the contents of the folders to the folder that you want to keep. You
can disconnect from the other pst-files by rightclicking the root folder and
Robert Sparnaaij [MVP-Outlook]
Tips of the month:
-What do the Outlook I...Excel 2003 Professional Edition - Exporting XML Data
This is a multi-part message in MIME format.
I have optional elements in my schema. Presently, if the cells mapped =
to the attributes of the optional elements are left blank, the elements =
are written with "0" as the attribute (see example below). However, I =
want the optional elements ("Element1" and "Element2" in the example =
below) to be omitted from the exported XML file.
For example I get ...
<Properti...Data Lables, in chart go away when reopening
I've got a pivot table with the sales reps name in the row, month sold in the
column and for different sales figure "sums" in the data.
I've created a standard column chart to compare these 4 different "sums"
(which become my category axis), by rep for a given month and even though I
format the data series to have data labels (value or $), everytime I close
and reopen my workbook the data lables are gone.
Is there anyway to make these lables stay so when I reopen my workbook they
are still there?
...Display multiple data
I work as a paramedic, and one of my many task is to register all our
activities. I use a excel sheet to collect all data.As we are many people
working there, this must be simple.We put all information for one year in one
page, may be 4-500 items, each with 25-35 data.
No my problem:
I need a way to find all info for one month or employed,and create a new
list with these .
My dream: I write employed number 245 in cell B6, and the all his data
displayes underneath.... (excuse my bad english...)
If you install my Filter Add-in it is very simple to filter in place or copy ...How do can I create a Excel worksheet from information on a templa
What I want to do is to automatically list information from my Purchase Order
Template into an Excel Spreadsheet.
I have many too many queries, which I use to gather data for a report. I
hope someone can suggest how to consolidate them. For example, 8 of my
queries are of the exact same form, except the value of one field is one of
8 possibilities. Here is the SQL for the qCUS query:
SELECT q1Main.DReferred, q1Main.CaseType
WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And
The only difference between the 8 queries is that in the qCUS query,
CaseType = "...AutoComplete in a large worksheet
I have over 1,000 hymn tune names in a worksheet, and when I'm adding new
entries to the bottom of the continous column of names, AutoComplete
sometimes works, sometimes doesn't. It always works when I'm entering
something that duplicates another entry I've made during that particular
session, but not always when I'm duplicating data from a previous session.
There are no breaks in my column, and AutoComplete is enabled. Is the size
of the sheet the problem?
it won't work if there're blank rows in between
Frankfurt, Germa...Word field codes in Excel data file Includetext
Excel 2003 on XP
I want to use an Excel spreadsheet as a data file for a merge project that
is too large for a Word table, but I need to use Word field codes, expecially
Includetext, in the data. If I try to paste, or use paste special options,
with a field code created in a Word docment into Excel I get the resulting
text, not the code I want.
as a starting point:
"mranz" <email@example.com> schrieb im Newsbeitrag
news:2D4F5F6F-6F4E-4DF2-B840-675AACE815B7@micros...changing data across all worksheets
I asked this question before, sort of, but here is a different way to put
I have an address list that is rather large and I want to do several
worksheets, with the first being the complete list and the rest being sorted
and hiding some rows, such as duplicate entries. I want to be able to make
changes on the first worksheet and have them automatically carry over to the
rest of the worksheets, such as when someone sells their property and the
name needs to be changed. Currently I make the changes individually but that
is getting too time consuming anymore. Does anyone have any ideas, or sh...Access CRM data for outside the network
I would like to know if it is possible to access my CRM data outside of my
network (via a web page NOT VPN). I would like customers an other
consultants that work for me to have access to the data from anywhere any
time with the proper credentials of course.
I attempted to configure all unassigned IP's in IIS but I was unable to
access it externally.
I am using CRM 3.0 on a SBS 2003 Server (plain vanilla configuration).
Any Ideas or how to's on this?
Please and Thank you,
There are few options regarding this.
1. View webpage, you have to make it ...Consolidation Worksheet Data Sequence
I'm consolidating 3 worksheets into one by using Excel 2007 data
consolidation. The worksheets have a product number in column A and sales
figures in columns B, C & D for Jan, Feb and Mar. The worksheets are in
product number sequence. Not all product numbers appear on each sheet, so I
consolidate by category using "labels in left column" and "create links to
source data" to create a consolidation sheet in outline form. The problem is
the consolidated worksheet is not in product number sequence as I need it to
be. I have books on Excel 2007 and Excel 2003 and their ...