Vary Color based on value
Is there a way in a bar chart to have a bar be a specific color if less than,
lets say "5" ? Instead of manually changing each bar that is less than a
Create two additonal columns of formulas, suppose your original data is in
B2:B12, then in C2 enter the formula =IF(B2<=5,B2,0)
in cell D2 enter the formula =IF(B2>5,B2,0)
Copy these formulas down.
Plot these ranges instead of your original range. Format one series one
color the other another.
If this helps, please click the Yes button.
This is a Label in ItemTemplate
How can i take the text value of <%# Eval("Organization") %>?
put this - onmousedown="alert(<%# Eval("�ӽо��c") %>)" ???
thanks for any ideas?
<asp:Label ID="Label11" runat="server" Text='<%# Eval("Organization") %>'
<asp:Label ID="Label2" runat="server" Text='<%# Eval("Depart...Is it possible to get text values in a pivot table?
ROW COL VAL
A HD1 X
B HD2 Y
B HD1 Z
C HD3 J
A HD3 Z
B HD3 R
C HD1 S
Var of VAL COL
ROW HD1 HD2 HD3
A X Z
B Z Y R
C S J
(If 2 rows go to same cell, would need to get maximum or first).
Not with Pivot Table
Let you A, B C data be A1:A7, the HDn data in B1:B7 and the other data in
In some convenient place (I use A11) enter =A1&B1 and copy down the column
(you could put this in another sheet or in a hidden column)
Make the heading HD1, HD2 - I did this in F1:H1 and the row ...Referring to a Text Box not the value of the Text Box in code
We’d like to store the reference (for lack of a better word) of a control in
a variable for future use. For example:
Me.Suffix1 is a text box on a form so things like Me.Suffix1.SetFocus will
set the focus to the text box. When we loose focus on this text box we’d
like to do some like:
ctrLastFocus = Me.Suffix1
where in a module we have
Public ctrLastFocus as Control
Then elsewhere we could write something like: ctrLastFocus.SetFocus
Of course this doesn’t work as Me.Suffix1 isn’t the control, but the value
of it. How do you do this?
If what you want to know is wha...How to stop Pie Chart from using N/A# for values
I have 2 columns: Column 1 is customer name. Column 2 is sales for the
If a customer had no sales for the period the sales column will either be
N/A# or zero. When I plot in a pie chart, I only want to show customers who
had sales for that period.
Is there a way to only plot those customers with sales without having to
change the range and sort every period?
The pie chart data labels show the customer regardless of sales and if I
blank out the customer, the value data label s...Return Range Based on Cell Value
This topic has been covered in varying degrees, but the problem is
that I do not want to sum or count the range. I want the range itself
returned as the value.
For instance in A1 is 7. i.e. July
In B1-B12 are dates 201101, 201102, etc.
I need a formula that will count out the cells and return the range
based on that value in A1. In this case it would return B1:B7. This of
course would be nested in another formula.
On Tue, 4 Oct 2011 17:18:20 -0700 (PDT), Pablo < > wrote:
>This topic has been covered in varying degrees, but the problem is
>that I do not want to sum or count the...Combo box returning wrong value
I am having trouble with a combo box, which is filled by a query to that
displays two fields (Fund # and Fund description). The fields are unbound
and I am using VBA to store the unbound fields in a table. I am using an
option box to determine which sequence to use to present the data (I use two
different queries and store one of them in the row source of the combo box,
depending on the option box value). This all works fine. The problem comes
when an item is selected. I would like to store the values both the Fund #
and the Fund value in my table with VBA. My VBA code in the co...Passing Values between forms
I am trying to link to forms. I have a button on the main form
(frmCompanies) that needs to open another form when a button is clicked. The
new form that opens is frmScheduledActivities. When it open it needs to check
to see if there is an existing record (scheduled activity) for the company
and if there is no scheduled activity be set to add a new record.
When the button is clicked on the main company form (on the on click event)
this code runs:
Private Sub cmdCallBack_Click()
On Error GoTo Err_cmdCallBack_Click
Dim stDocName As String
Dim stLinkCriteria As String...Null value
I want to check if there is no value return...how should I write this line of
strPartNumber = Nz(DLookup("PartNumber", "Kanban", "PartNumber='" & Me.
txtPartNumber & "'"))
Message posted via http://www.accessmonster.com
Yup..thanks for your help...
Ofer Cohen wrote:
>strPartNumber = Nz(DLookup("PartNumber", "Kanban", "PartNumber='" & Me.
>txtPartNumber & "'"),0) ' Replacing Null with 0
>' Check for zero which mean no record was ...Return Value from Form
I need to return a value from a form displayed modally.
Many years ago, if I used VB classic, I could create a public method that
took care of any arguments, displayed the form modally, and then returned
appropriate return values. Something like this:
Public Function GetValue(Args As Integer
m_Args = Args
m_Result = DefaultResult
GetValue = m_Result
However, it appears Access VBA doesn't support this. For one thing, there's
no Show method (and so it certainly can't have a modal argument).
I like this approach because ...graphing ordered non-numeric value
How does one graph an ordered but non-numeric value (e.g. Excellent, Very
Good, Good, Satisfactory, Fair, Poor, Failed) on the Y-axis and the date on
> How does one graph an ordered but non-numeric value (e.g. Excellent, Very
> Good, Good, Satisfactory, Fair, Poor, Failed) on the Y-axis and the date on
> the x-axis?
Probably the easiest way is to give each of them a number (e.g. Failed = 0,
Excellent = 6) and plot those. Then format the Y axis to show markers but no
labels and use text boxes for the labels.
Microsoft MVP - Excel...Selecting Maximum Values in a Query with joins to other tables
I'm having difficulty with a query and hope someone can help me out.
Basically, I'm looking to select the maximum amounts from a table based on a
column in one of the tables named StateID.
It works fine when I do this:
SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount)
AS Amount, dbo.States.StateName
FROM dbo.Bids INNER JOIN
dbo.States ON dbo.Bids.StateID = dbo.States.StateID
GROUP BY dbo.Bids.StateID, dbo.States.StateName
ORDER BY dbo.States.StateName
However, when I start to join fields from other tables in...Does batch recovery result in different transaction source values?
We have a situation that we can't explain. Here are the facts:
1. User posted a Receivings transaction batch comprised of 37 different
2. In the G/L, transactions #1-32 have a ORTRXSRC value of 'RECVG00001854',
a TRXSORCE of 'GLTRX00037046' and an ORGNTSRC of 'RECVG00001854'.
3. In the G/L, transactions #33-37 have an ORTRXSRC value of 'GLTRX037046',
a TRXSORCE of 'GLTRX00037047', and an ORGNTSRC of 'GLTRX037046'. Note the
slight difference (one less zero) in the TRXSORCE and ORGNTSRC values
compared to transacti...How to retrieve the pointer to leaf node of a Binary tree
How to retrieve the pointer to leaf node of a Binary tree
This is how the binary tree looks like...
Node* GetLeafNode(Node *t)
The GetLeafNode method given above returns the pointer to the root node.
How to retrieve only the pointer to "e" node.
Your post is a little ambiguous.
You want a leaf node to be ret...New entity and relaitonships customization
I am working on a CRM v4 system that needs to track government funding
programs for doctors at health clinics. The system is set up so that
Accounts map to Clinics, and Contacts map to Doctors. The essential process
A funding object is created, is set to run for a certain number of payments,
is set with a start date, periodicity of payments (i.e. weekly, monthly,
etc.), and end date, and then is assigned to a doctor at a clinic. If the
doctor leaves the clinic, the funding object transfers to the clinic and is
suspended until the clinic hires a new doctor and then the fund...dates as minimum axis values
Excel versions prior to Excel 2007 allowed dates to be entered as minimum
axis values for XY charts on the axis format scale menu. In Excel 2007 when
I attempt to enter a date as a minimum value, i.e. attempting to enter
1-1-2007 with the Fixed radio button selected in Axis Options, the automatic
value pops back up unchanged. I can successfuly enter a numeric value, but
not a date. This requirement adds several extra steps - calculating a date
value and typing the resulting number, then deleting the calculation - to
what used to be a simple process. If anyone knows that the same cap...Look up values and place in spreadsheet
I have an excel workbook with two sheets.
On Sheet2, in column A is employee name. In column B is comments about that
employee. The employee's name could be repeated several times throughout the
workbook. Sheet2 has 1000 rows with multiple employees on it.
On Sheet1 I want to put all the comments that match Employee1 (which is in
cell A1 on Sheet1). There will probably be 20 matches, so I want the
comments to be in rows 2-21.
Is this possible?
Thanks in advance.
Try this *array* formula in A2 of Sheet1:
=INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$30=$A$1,ROW($A$1:$A$30))...Updating External Link Values.
Im sure there is an easy solution to this but im banging my head
against a brick wall here!
I have a cell which changes each week to reflect the week number "Week
I have an external link to another workbook in the format:
=IF(F5=0,0,'H:\Desktop\Apps\[Bus Week 25 Stats.xls]Week'!F$5)
Basically I just want to be able to get that "Week 25" to change.
To check the value in F1 (which has the Formula which creates "Week
=IF(F5=0,0,'H:\Desktop\Apps\[Bus Week 26 Stats.xls]Week'!F$5).
Sounds easy huh! Im stuck!
Help! :eek...Changing format of number without changing the value
How do I add the % sign to a existing number without changing the value?
Example: 1.68 should show 1.68% not 168?
Select the cell & press Ctrl-1
Navigate to the Number tab
Select Custom in the left hand panel
In the Type edit box enter 0.00"%"
Click on OK
> How do I add the % sign to a existing number without changing the value?
> Example: 1.68 should show 1.68% not 168?
Percentage-formatted cells are based on 1 for 100%. It's only when you key
in the percentage that it's converted, for example you key 1.68%, and it...VBA coding to retrieve data from Access to Excel :S
This is what i have so far. I have an excel workbook with 3
worksheets, SETUP, DATA and DISPLAY. I have been using a pre-coded
program to bring data from a database where the program using the SETUP
sheet from the excel workbook as its critaria and then populates the
DATA sheet. I then can use the DATA sheet to display data in the
DISPLAY sheet in which ever way i want.
Complication is that this pre-coded program is limited to the amount of
tables it can hit at a time. Basically its programmed to read up to 3
I now need data from 5 tables all together. The first t...Make Bit field control value of float field
I've made bit field with the default options of yes and no: new_Q1
Then i've made a field to collect a amount of points, incase the bit is set
to yes: new_Q1p.
If i select the format of the bit value to "list" my script works fine. If I
select "check box", it does not work.
Can any one help me modyfi the script to work with the check box?
Regards, Jacob Mondrup
This is the script I use:
var point = 0;
var get_status = parseInt(crmForm.all.new_q1.DataValue);
switch (get...Crosstab Row Heading Issues
I have Access 2003 and when I do a cross tab I cannot bring in more than 3
field values for row headings - my coworker (who has Access 97) can bring in
>3 row headings. Is this a paremter setting in Access 2003?
I believe that the Wizard can't handle more than 3; however, you can do more
with SQL. Try creating a crosstab with the three headings, them modifying it
at SQL level to add more.
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> I have Access 2003 and...Append values and remove text
I have a number of columns - some have values and some don't. I wish to
append only the columns with values and have comma separators.
Is there an easy way to do this? Currently, I do this in 4 steps but I'm
hoping there is an easier way.
1) Column EQ has a formula to append values in columns R thru Z
2) Column ER looks at EQ and substitutes " ," with blanks. Unfortunately ER
always ends with ", " PLUS if only one column has any values, then ER also
starts with " ,"
So to fix this, I do 2 more steps:
3) Column ES looks at ER t...Resetting values based on change in cells of other column
I need help with doing the following logic in my worksheet
A B C
1 1 1
1 2 2
1 3 3
2 4 1
2 5 2
I want column C to restart its incrementing whenever column switches to a
Any help would be appreciated.
From you example, you want column C to increment unless Column A changes
manually put 1 in C1 first
in cell C2 and copy down.
> I need help with doing the following...Build value from cell content & sheet name !
Interesting query (I hope)....
I'd like to create a value in a cell that represents a concatenation of a
value in another cell and the sheet name (tab name).
Example, sheet name is 'A' and value in cell 'M15' is '1162'.
I'd like cell 'M18' to indicate a value of '1162A'.
Any ideas ??
to get sheet_name see http://www.xldynamic.com/source/xld.xlFAQ0002.html
(remove nothere from the email address if mailing direct)
"Neil" <firstname.lastname@example.org> wrote in message