Auto Filter based on cell value / drop down list
I have a spreadsheet that holds quality data for multiple products
indexed on the date and time of production. Data from this is charte
so I can see chronological trends either for all products or selecte
ones using the autofilter. This currently works by placing the graph
over the data table and setting them so they dont move with the cell
What I want to do is put the graphs on a separate sheet in the workboo
with a drop down list to select the product to chart.
How can I get the auto filter to operate based on the value selected i
the other sheet? Does this require macros or VB ...Cell value prefix
I'm looking for a way to implement this ...
When a user selects cell A1 and enters a value (text), I would like the
displayed text to display a prefix also.
How can this be achieved?
eg. If user types in cell A1 the text value "Paul", when the enter key is
pressed, I would like the cell value displayed to be "England - Paul".
Likewise for example typing in "David" would give "England - David".
Cand this be done in using VBA or cell formatting?
(Remove [nospam] from e-mail address when corresponding by e-mail)
For VBA (y...Retrieve value from parameter in bound query
I have a report bound to a query that has a parameter filter on a date field.
Is there any way to retrieve the value that the user enters into the
parameter dialog box?
I've tried accessing the Parameter object from the QueryDef, but the Value
property is always empty during Report_Open or Report_Activate (the former
occurs before the dialog is presented anyway). I also can't just construct
the query SQL with VBA, as the query references multiple queries all with the
same named parameter (I like how nested parameters don't require multiple
entries by the user if they are ...XPath
Using XPath I am querying parts of XML document, " get quoted by \ - is it
possible to specify not to quote " with \?
<Element Attribute="SomeValue" />
XmlDocument xmlDoc = new XmlDocument();
XmlNode xmlNode = xmlDoc.SelectSingleNode("//RootElement/Element")
where I was expecting to get
> Using XPath I am querying parts of XML document,...Getting a specific parent of any node via xpath navigation
I am storing my site structure as such:
In infopath, I use a XPath expression to get some nodes :
var oEMailNodeList = XDocument.DOM.selectNodes("//Message/EMail");
My problem is that this method returns 0 items. Here is the DOM.xml property
<?xml version="1.0" ?>
<?mso-infoPathSolution productVersion=\"11.0.5329\" PIVersion=\"22.214.171.124\"
solutionVersion=\"126.96.36.199\" initialView=\"messagesList\" ?>
<?...Dates and Cell Values
How can I get the minimum value from column C for all 8/24/2009 dates in
I tried some index and match functions but still doesn't work.
Column A Column B Column C Column D
8/24/2009 12:12:56 AM 113 904
8/24/2009 12:52:56 AM 114 908
8/24/2009 1:32:56 AM 114 907
8/24/2009 2:12:56 AM 112 897
8/24/2009 2:52:56 AM 113 902
8/25/2009 12:13:57 AM 82 654
8/25/2009 12:53:57 AM 81 650
8/25/2009 1:33:57 AM 81 650
8/25/2009 2:13:57 AM 81 650
8/25/2009 2:53:57 AM 81 649
8/25/2009 3:33:57 AM 81 647
8/25/2009 4:13:57 AM 81 646
8/25/2009 4:53:57 AM 81 643
I am creating a sheet to log sales. One of the columns is a drop down list to
select Cash, Cheque or Credit Card. The adjacent field shows the value paid
by that method. There will be a number of rows of data.
I wish to do a formula at the base of the spreadsheet to show the total cash
sales, total credit, total cheque. Any suggestions on how to do this (I am a
The SUMIF function should work for you here:
This searches for the text string "Cash" in Column A, and if found, adds the
corresponding value from column B. Modify...how do I lookup multiple values
Trying to lookup a value based on both Horizon and Vertical columns….
A B C
1 JAN FEB MAR
2 Ice-cream .5 .6 .7
3 Hot Cocoa 1.0 1.1 1.25
4 Coffee 1.5 1.3 1.0
5 Soda .8 .6 .9
How do I look up the price of ice - cream in the month of Jan. or lookup the
price of coffee in Mar based on an input from user on both product and month?
I can’t figure out which function to use. Please advice.
(remove nothere from email address if mailing direct)
"Lisa" <Lisa@disc...format merged value in word
In older versions of word & excel, a value formated in Excel would hold its
format into word through a merge. I need to merge a percent 1 decimal place,
but the resulting merge prints to 10 decimals. Help says to format the value
in Wrod, but I can only format words, not values. Any help?
There's an article on the Microsoft web site that might help you:
Answer Box: Numbers don't merge right in Word
Dr. Pete wrote:
> In older versions of word & excel, a value formated in Excel would hold its
&g...Compare/Sepearte Values from Cell
I have a set of survey data in Excel format. Some questions are in the format
of "check all that apply" and the answers i have are in a single column of
cells with each answer seperated by a coma. The answer could also be "other".
For some reason, the survey doesn't mark the answer as other, but simply
record the data, so i can have answers like
C,E,custom input data1
A,custom input data 1
I want to get the data into seperate columns with binary indicating values
A | B | C | D | E | Other | Value of Other
1 1 1
1 ...How to pass a Nodeset to an XPath extension function?
I am using the .NET framework v2.0.40607 with Visual C# Express and I would
like to create some custom XPath functions to use directly in a XSLT
I called System.Xml.Query.XmlArgumentList.AddExtensionObject(string, object)
to register the functions I've written.
The functions that take a String, Boolean or Number parameter work
perfectly. However, I do not know how to create a function that will accept a
Nodeset as a parameter.
I tried writting a function taking a XPathNavigator object, but it does not
work for all XPath queries that return a Nodeset: I get an error &qu...Return Value in Cell Diagonally opposite
Can anyone give me a formula that will return the value in a cell that
is diagonally opposite. If the value I search for is found in say B2 I
want to return the Value in C3.
See if this does what you want...
A1 = lookup value
Result = Y
Microsoft Excel MVP
"Pete" <firstname.lastname@example.org> wrote in message
I'm looking for a way to Validate an XPath prior to executing the
expression, without using a try catch clause. For example: I'm looking
for something that would tell me that "\some/Xpath\to\Node" is
As far as I know, the .NET framework could not validate XPath directly with
class library. The only thing we can do is to use try/catch block. Or you
can try to find some 3rd-party tool such as XML spy to validate it. HTH.
"This posting is provided "AS IS" with no warranties, and confers no
Thanks K...Conditional Formatting A Cell Based on Another Cell's Value
I have a report where I want to use conditional formatting on the two
far right-hand columns.
Applying conditional formatting to right-hand column 1 (RHC1) which
consists of percentages is easy. If the value in any cell in this
column is less than .85 the cell is shaded green, if greater than or
equal to .85 and less than or equal to 1 the cell is cell is shaded
yellow, if it's greater than 1 it is shaded red.
Right-hand column 2 (RHC2) consists of whole numbers. I want the
shading of the cells in RHC2 to match the shading of the cells next to
them in RHC1. I have tried to create formu...Xpath with namespaces
I am having an issue with xml and namespaces, at least I think it is
namespaces. When I use namespaces, I cannot use SelectSingleNode /
SelectNodes as they always return 0/Nothing respectively. Anyone have any
Thanks in advance
Dim objXML As System.Xml.XmlDocument
'Test XML 1 ==> Always returns 0
objXML = New System.Xml.XmlDocument
objXML.Load(Application.StartupPath & "\xmlfile1.xml")
'Test XML 2 ==> Always re...replacing values in cells from a pre-designed table
I am working with an Excel Spreadsheet. I have, in column C, a different value on each row: 1.2 or 0.7 or 2.5, etc. In column D I want to have Excel automatically place, in each row, a new value based on a reference table I would create.
If column C value is Then column D value on same row will b
...Integer maximum value
I have define a field type as integer.
I tried to put a value 120,000 but it seems exceeds the value.
An integer field stores numbers from -32,768 to 32,767. A long integer
field stores numbers from -2,147,483,648 to 2,147,483,647. So, just change
the field size from integer to long integer, and you should be OK.
Help on Field Size gives the specifications for all the data types
"Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote in message
>I have define a field type as integer.
> I tried...Plot X and Y values
I need to create a chart in Excel in which each point has
an X and Y value, reading the info from a table of data.
When using the "scatter" chart type, it doesn't do that,
instead it draws two series' of points, one series for the
X values and one for the Y.
There may be some data in adjacent columns that is confusing the Chart
Wizard. Select only the series data and labels, and run the chart
wizard, and it should set up the chart correctly.
> I need to create a chart in Excel in which each point has
>...Updating value of asets in Money
Ive been useing Money for some time now,upgrading each
year and learning as I go.
Id like to update the value of several assets (cars ,
home , ect) in money totrack their worth. My question is
how do I do this? The program asks for a catagory? Which
do you suggest?
I'm new to Money but I've been suing a category I call
Depreciation for this task for years in Quicken. I just
write all changes in value (up or down) to this category,
and then do not include it in my cash flow or budget
reports (since it's not really money I'm spending or
making until it's "re...Tricky XPath query
Hi all, I've just changed the structure of my XML file and now I need
to construct an XPathNavigator object that uses tags from two
different levels in the XML file.
Some Data That Belongs Here
Some Data that Belongs More Specifically Here
This Data should real...X axis values
hi, just wondering if anyone knows how to alter the values
at the bottom of a line chart? i have a set of data and
need to start the set of data from 16-30 without much
luck! as opposed from 1 -15! anyone help??
If you did not specify category labels in a chart, Excel will just
assume you want 1, 2, etc. Presumably you have the numbers 16-30 in a
range. The best thing to have done, and to do in the future, is to put
the category (X-axis) data in a column just left of the Y value columns,
put labels at the top of the Y data and a blank atop the X data, select
the range and make your ch...Need to sum values that are not strikethroughs
I need to sum a column without it adding the rows that have strikethroughs
values; is it possible to combine a formula and vb code to a cell ?
=SUM(BH100:BH120) & Activecell.selection.font.strikethrough = false
Function zum(r As Range) As Double
Dim rr As Range
For Each rr In r
If rr.Font.Strikethrough = False Then
zum = zum + rr.Value
In the worksheet use it like:
> I need to sum a column without it adding the rows that have strikethroughs
> values; i...Multiple If True Values?
Does anyone know of a way to check for a value in two different cells, and if
a certain combination of values exists, change the format of another cell?
Any help would be much appreciated.
Start by experimenting with Format|Conditional Formatting
Then come back if more help is needed
Bernard V Liengme
remove caps from email
"Lee" <Lee@discussions.microsoft.com> wrote in message
> Does anyone know of a way to check for a value in two different cells, and
&g...Want to show a group header item only when the group value changes
We have a couple of reports that show lines for a customer order.
We were told to add notes for each line that were entered in our oe
portal so our manufacturers and shippers could see them. I've done
this but the group header for an order ine is set to repeat so the
note shows on every page.Each line can take up several pages due to
the fact that lot pick information is displayed for each line.
When the notes are long, it can create a report that is very long,
simply due ot the extra space need on each page. We would like the
line note to to appear ONLY when the va...