Limiting formulas based on filtering?
Is there an easy way to build a formula that will only take values into
consideration when the auto format is used. So, if I have a table of data and
a formula based on this table and I filter one of my data columns to some
vause then I would like my formula to automatically update.
Is this possible?
The SUBTOTAL function ignores rows that are hidden by an autofilter (and
optionally, also rows that were hidden manually). It can perform a wide range
of operations, including sums, averages, counts, standard deviations,
products, maximums, minimums, etc. Check E...control charts #2
Might some direct me to some good examples of control charts and or
directions for creating.
I have been asked to get up to speed on them and this is somehting new for me.
Jon Peltier has information on creating them in EXCEL,
I'd suggest you do some reading on control charts from other sites. There
should be something here that could help you.
"Todd F." wrote:
> Might some direct me to some good examples of control charts and or
> directions for creatin...Paste Filtered Range to New Workbook- AS
I recorded a macro and additionally used the DG help to construct the
following macro; however, I'm not certain how to copy the filtered range
(which will vary every time the worksheet is used) or how to find the last
row of the workbook to which the data will be pasted. Any help would be
great! And thanks in advance.
Dim wbname As String
Dim copyrange As Range
Dim LastRow As Range
Dim rng As Range
' Macro1 Macro
' Macro recorded 12/8/2009 by asagay
wbname = ActiveSheet.Range("g1").Value & ActiveSheet.Range("j1&qu...Dynamic series in Chart
I have a spreadsheet with timestamps on col A, then Col B contains the
values correspond to timestamps in ColA (Col C thru Col M are similar to Col
B as well). I am trying to build a chart in function of time (meaning
Values for the X axis would be B2..M2, and Y axis would be B3..M3 for the 1
series, then B3..M3 for the values in x-axis, and B4..M4 for Y axis, so
Number of rows is subject to change (time stamp entries), and I am only
interested for the last 21 entries (if there is less than 21 entries, plot
them all, otherwise, only plot the last 21 entries). For ...Chart update automatically
Sometimes I start a data list to chart, create a chart then add data and the
chart automatically updates as I add to the data list. The X axis grows with
each entry. Other times, I do what I think is the same thing but the chart
lays dormant. I actually have to manually update the series range. Nothing I
do seems to make it "automatic". Is there a way to make any chart (new or
existing) update automatically when dynamic data is involved?
The chart will update automatically if,
- you based the chart on a List (Table in xl2007)
- created and used a dynamic named range
- i...filter birthday's by month in Excel
I have tried the above by auto filtering, then custom filter, however when i
type for example ??/07/?? or **/07/** nothing works. Could you please tell
me how to filter birthday's by month as soon as possible.
I don't know a way to do it directly. As a work-around, you could add a
column and fill it down with the formula (assuming the dates are in ColA,
starting at A2) =month(A2).
Then filter the database based on the new column.
"Lisa Peterson" <Lisa Peterson@discussions.microsoft.com> wrote in message
news:4C9E7EAE-9FD...Modal Dialog OnCancel removes SC_CLOSE
I have an app with an modal dialog in which I have removed the default
cancel behaviour by creating an empty OnCancel().
Now I can't close the dialog using the SC_CLOSE (Red-X in upper right
Why and how can I remove ending the dialoq by pressing Esc but still be
able to close the dialog by clicking on the Red-X
English is not my first, or second, language
so anything strange, or insulting, is due to
Please correct me so I may improve my English!
 if someone has a official name for this button I would be grateful!
Anders Eriksson s...Removing hyperlinks in Excel by multiplying by 1 is very obtuse
I just followed the Excel help directions for "Remove a hyperlink" ->
"Deactivate several hyperlinks at once". And while the directions work
(multiplying each field by 1), they strike me an obscure work around to a
rather obvious missing feature. It should be easy to remove hyperlinks from
text in Excel, especially because Excel is very agressive about auto-creating
hyperlinks when any text that resembles a web address is entered. Hopefully
this missing feature will be addressed in the next verison of Excel.
post your suggestion to:
Reg...Select multiple adjacent cells of multiple cells without selecting
Select multiple adjacent cells of multiple cells without selecting adjacent
cells one by one. those cells looks like;
|adjacent cells |cells|
|some characters | A |
|some characters | B |
|some characters | A |
|some characters | A |
|some characters | B |
I am trying to select adjacent cells of A cells without selecting adjacent
cells one by one.
Thank you for your help.
Adjacent in this case, means?
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgr...Need to remove help compile in project
Can someone tell me how to remove the help build process from a project?
What does this mean? You want to remove part of the build? If yes, just
remove those files from project.
Ajay Kalra [MVP - VC++]
"mike" <nospamplease.com> wrote in message
> Can someone tell me how to remove the help build process from a project?
I've tried removing the help files but when I build, the help compiler is
still invoked. The project show a couple of .rtf files in the dependenc...Remove records from lookup
Not sure if this is possible but I would like to remove all records except
the account records from the look up records dropdown menu of the
appointment entity. The associated schema is regardingobjectid and the label
is regarding. I see the other entities listed under relationships but there
is no option to delete. Is there a way to create a new lookup and have it
added to my appointment form?
There is no supported way to change this. The system is setup to allow you
to set appointments that are related to specific records (Opportunities,
Quotes, Cases, etc) and it will then ro...Removing an exchange server member
I have a 2000 exch backend and a 2003 exch frontend setup. The front end hard
disk just recently died. And so was not correctly un installed from AD. There
are still elements of exchange hanging around. For instance, in the system
manager. But the system manager wont let me delete the front end server
under the first administrative group. Do i have to use adsiedit to remove
this or is there a much simplier way. Cheers Paul
To my best knowledge, it adsiedit.
"Yakeni" <Yakeni@discussions.microsoft.com> wrote in message
news:B352B981-F5B7-4527-8A48-04E3976C99D9@microsoft.c...Checkbox event to control other checkbox selections
I have been working for a couple of hours now trying different ways to
solve this problem and I'm out of ideas (or tired). :-/
Here's what I'm trying to do:
On a form I have a section with six checkboxes that represent 6
different mailing lists an organization can subscribe to: MailList01,
I have a seventh checkbox named MailListOmit for orgs that want to opt
out of mailings.
If MailListOmit is checked, when the user attempts to click any one of
the MailList checkboxes, I want to return a dialog box that says,
"This organization has chosen to not receive ma...Scrolling chart
I am confused where the reference range SCROLL_DATA and SCROLL_LABELS
slamm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15018
View this thread: http://www.excelforum.com/showthread.php?threadid=382363
In Insert, Name, Define, (the normal place to find a named-range), an
also read the Offset Worksheet function in Help for furthe
Hope this help
---------------------------------------------...How to share info from one file to another?
I am wondering how I can share something perhaps from a cell of "file 1
to "file 2." I am familiar with how to do it from worksheet t
worksheet within a single file. From what I have heard I know that i
can be done but both files have to be located in the same folder.
Correct me if I am wrong and please tell me briefly how it can be done
I appologize if this is a newbie question. Thanks Chip Carlto
ChipCarlton's Profile: http://www.excelforum.com/member.php?action=getinfo&useri...How do I stop axis titles from overlapping with negative chart da.
The negative bar charts overlap with the category titles. I've tried offset,
but it won't let me move the text more than '1000' units, and it still
You can find an explanation here.
> The negative bar charts overlap with the category titles. I've tried offset,
> but it won't let me move the text more than '1000' units, and it still
Andy Pope, Microsoft MVP - Excel
Hi Karen -
Double click on the category titles o...xy Scatter chart #3
I will try to explain this to the best of my knowledge:
I have a spreadsheet with some information that is set up like this:
Example Risk Impact Probability
Director Trls Med 3 1
Wrong Trips High 4 3
Upgrade JAX Low 1 1
These numbers are generated from a series of questions that determine wether
the risk is a high, med, or low factor. So there is really no way of knowing
what numbers determines a Low, Med, or High Risk The numbers will never go
higher than a 4 and lower th...Charting depending on criteria & data series name as a column val
I want to chart some prices as I want to take a look at price trends.
My problems, and I can not figure out how to solve them, are:
1. Is it possible to dynamically change the chart depending on certain
criteria (product family & selected customers)
2. As the number of customers is variable and they are in one of the
columns. Is it possible to plot a series (customer name), depending on the
name of a column?
3. I want to chart the data based on the date, but just include the dates
available, to prevent periods of time showing no change (e.g. I don't want
monthly ticks for the x a...How do I add an indicator line to a Gantt chart in excel?
I have created a Gantt chart in Excel. I would like to indicate a specific
date - most of the time today's date - by a vertical line showing how many
days have passed and how many yet to go for specific tasks. I have attempted
to create an 'XY graph' on top of the bar/Gantt chart, but the two do not
seem to be compatible.
See my examples of various ways of adding a datum line to a chart. I'm
pretty sure one of the techniques should work for you.
> I have created a Gantt chart in Excel. I...Click on a Pie Chart
I'm pretty sure this isn't possible but.....
I've been asked if it's possible to click (or double click) on a slice of a
pie chart and jump to the data it represents (the same as you can in a pivot
I can't speak for 2007, but I recall a few years ago I was looking at making
a pie chart clickable to "drill down" in 2003, updating the pie chart to show
the component measures of the wedge that was clicked on. It appeared to be
possible using VBA, but then the project was terminated before it really
started. I can...Previous year data into chart
I currently have a whole table of data imported into Excel from Access.
The only problem is, i cannot seem to import the data for the previous
year using a query. With the data i would like to create a chart for
the past 12 month, i.e the last 12 cells (as the data is taken
monthly). Can i select data for the last 12 months that will remain the
last 12 months even when the worksheet is refreshed every month?
How my data looks:
Date % Acknowledged % Investigated
10/2005 ...How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When
generating reports, I would like to filter per agency and display only the
rows associated with that agency.
Is there a tutorial or sample on how to do this?
It sounds like you are looking for Data / Filter / AutoFilter. Have a look
here for some basics:
"Jack" <firstname.lastname@example.org> wrote in message
> I have a spreadsheet that contains multiple agency id's in a column. When
> generati...How do I remove a hidden space in Excel cells
Not sure if this is an Excel or Word problem...
I am doing a mail merge in Word (envelopes). I am pulling my data (name,
address, cit/state/zip) from an Excel .xls I created.
In mail merge, I select envelopes and go through all the steps.
I use the Address Block for the delivery address. However, when it pulls
the data over, the name line (first line of address block) is indented by one
space. No matter what I do (place cursor at indent and use backspace or
place cursor at next two correct lines and add a space), the change doesn't
take place. I've checked all the tabs as...OE removed access to unsafe attachments #2
How do I tell Outlook Express to consider some attachments
safe and let me open them? Anyone know?
Tools-> Options-> tab Security-> uncheck Do not allow...
Note that this is an Microsoft Office Outlook forum.
Outlook Express is family of Internet Explorer and Outlook of the Office
Here is the link for the right forum
Microsoft Office and Microsoft Office related News
Also Outlook FAQ, How To's, Downloads and more...
Tip of the month:
-Create your o...OUTLOOK Attachment Filter
How do I modify, turn-off, or override the stupid Outlook attachment filter?
If I want attachments to be blocked, I want to have the choice, not this
program from Microsoft.
I've already lost one important attachment. How do I get around it, or kill
The PCMagazine site has a tip that deals with this. It
requires you to edit the Registry. (www.pcmag.com/tips/)
is the site. Outlook 2002 keeps you from opening
attachmentw with extension like .exe or .bat because of
the potential for viruses. If you are comfortable
editing the system Registry, you can tell...