Help Me to Conditions!
:confused: Hi there!
How can I set my Condition Formating at *Column C* (Item Name) to be
able to show indication (boxed RED) if one or more required infos (F &
G) is missing?
As *Column E* (Shipped Date) will be the trigger factor for these
*For exmple1:* Row 4, Orange is boxed RED becuase the shipped date was
entered, but QTY is missing.
*For exmple2:* Row 6, Durian is not boxed RED becuase there is no
|Filename: 123xx.doc ...sum multiple columns with SUMIF or SUMPRODUCT
I'm running Excel 2003 and am having trouble with what I thought should be a
relatively simple formula. Given a matching criteria in column A, I'd like
to sum columns B:D. I've taken a stab at it with the following formulas, but
they all return "#Value"
1st try =SUMIF(A1:A3="x","B1:D3)
2nd try =SUMPRODUCT(--(A1:A3="x"),B1:D3)
3rd try =SUMPRODUT((A1:A3="x")*(B1:B3+C1:C3+D1:D3))
I'm not sure i fully understand the question but how about this
...Exchange Migration Wizard #2
I think the answer is yes and yes, but just want to clarify. Can I use the
Exchange Migration Wizard to move mailboxes from an Exchange 2000 Server in
one domain, Exchange Org to an Exchange 2003 server in a separate domain,
Exchange Org? When I run it, it will create an appropriate AD account and
move the mailbox?
...Access Label Report Wizard
I have created a report to print address labels.
I am using a custom label.
The label wizard worked fine and I have a report that prints the labels fine.
During the creation of the Label report I got a message warning that there
was not enough horizontal room and some data is not in the printable area.
I ignored this error and copleted the wizard.
When I run the Label report now, I get this same warning regarding outside
the print area. Clicking help, suggests ignoring the message is generally
fine....which in my case is true as the labels print fine.
Is there a way fo...Conditional formatting help #6
I'm starting to bang my head against my desk with this one! I have 2 columns
of data: column A has 3500 rows; column C has 1800 rows. In column B I did a
vlookup so I know which cells in column A are in column C. The cells that
aren't in column C display #N/A in column B. I then copied column B and
pasted special as value so there are no formulas in column B. This part is
working as expected.
My problem is: now I want to conditional format column B so that all
occurrences of #N/A will be red shaded. For the life of me I can't get this
to work. Can someone please t...Conditional Formatting more than 3?
Excel 2000 ... Conditional formatting ... an abosolutely
wonderful feature, but why limited to 3 conditions?
Is there a way around this?
I have a Tab Sheet containing 16 monthly Calendars (dates
set by formula) ... These Calendars contain a target date
based on date entered into another cell ... Within the
Calendar I use Conditional Formatting to high-lite:
All dates ... (Format White) ... (Condition 1)
Target date ... (Format Yellow) ... (Condition 2)
Above said ... I have various +/- tollerance windows
around each target date (+/- window is ... 5, 14, 27, & 55
days) ... I have ...Conditional Formatting Problem
My intent is to apply conditional formatting to a text box when a control in
the same detail is blank. The text box (named text13), has a control source
=1 and a running sum Over All. The text boxes to the right are both named
Name and Type and have control sources of [Name] and [Type] respectively.
All data comes from the same query and all text boxes are in the detail
section of the same report named rptJuneTest.
For example, the report, when there are no null values may look like this (I
formatted the text boxes light gray, so the report shows rows of gray):
1 John Smith Pro...Wizard not available
When I try to open a new wizard template I receive an
error. The error is "Publisher cannot find a wizard
needed for this publication.This publication may have
been created in a differt version of Publisher"
This is the same version.
None of the templates will open
Did you do a custom install? What version Publisher? Have you tried detect & repair
found under help?
Mary Sauer MS MVP
"Rick" <email@example.com> wrote in message
news:19adf01c41cd...Conditionally format points based on a third column
I would like the color of my data points to vary based not on their x or y
value, but rather on a third column running alongside them. How can I do it?
Assuming your third column is column "C" and that you would want to change
the colors of the XY points based on the names Jim, Frank, or Kim, a VBA
option could look like this (does not include any error handling):
Application.ScreenUpdating = False
Dim Rng As Range
Cnt = 1
For Each Rng In Range("C2:C10")
Is it possible to automaticaly hide rows if all values(lets say B10:Z10) in
that row are 0?
For Each c In Range("a5:a15")
If Application.CountIf(Range("b10:z10"), 0) > 0 _
This will hide rows from 5-10 if col A is blank
For Each c In Range("a5:a15")
If Application.IsNumber(c) And c = 0 Then c.EntireRow.Hidden = True
"Michael" <firstname.lastname@example.org> wrote in message
I have created a list called 'FullName' which will be added to with a
When using this list in a cell using Conditional formatting is it
possible to have the dropdown list sorted in alpha order without
having to put the original list through a manual sort or macro sort
Any advice would be appreciated
...Date conditioning format problem
I am using excel 2000.
with my cursor in A1,
I select cell A1 to A20 which is already in date format,
and do a conditioning format(highlight) using IsFormula:
Unfortunately, all the cells from A1 to A20 are highlighted!!!!!! no
matter the dates is today or otherwise!!!!!!!!
In fact all the cells are having the same conditioning format
which is =$A$1=TODAY().
How to make it suvch that cell AX is having =$A$X=TODAY()conditioning
Take the Absolute off the row Reference
> I am using excel 2000.
>...Summing values in database according to multiple criteria
I have a spreadsheet;
A B D E
Dept Account Staff Amount
1 AA 1002 Peter 10
2 BB 1002 John 20
3 AA 1002 Peter 30
4 CC 1002 Paul 40
I want to sum the content column E by Dept (A), Account Number (B), and Staff (D). How can I do this by formula? As opposed to pivot tables.
Take a look at the SUMPRODUCT function.
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
"Peter" <Peter@discussions.microsoft.com> wrote in message
When I start publisher 2000, the microsoft publisher
catalog opens and informs me that the wizard files were
not installed and to run setup agian to install.
The problem is that when I try to run setup, it does not
work. If I try re-adding the media content, it goes
through the motions and says it's complete. I start
publisher and I get the same issue. If I try to repair
the install, it keeps asking for Disk 2. I have multiple
disk 2's from various purchased of the program and it
continues to look for Disk 2.
Any clues guys?
OFF2000: Error Message: The Feature You Ar...CRM should let me decouple conditional statements in Advanced Find
We have set up a number of saved searches in CRM 3.0 that users would like to
use Save As to create a copy and then add search terms to. Unfortunately,
CRM 3.0 does not allow you to decouple conditional statements once the search
has been saved. In order to add an AND condition, this feature is required.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the...Conditional Formatting #16
I have a rather large worksheet that will have hundreds of cells with
conditional formatting. Most of them are the same (only the cells and value
Here is an example of a conditional format I need:
If the value of B4 is between 96 and 96.04 then B44 is filled yellow.
> If the value of B4 is between 96 and 96.04
Assuming you mean >=96 and <=96.04...
Select cell B44
Goto the menu Format>Conditional Formatting
Formula Is: =AND(B4>=96,B4<=96.04)
Click the Format button
Select the Patterns tab
Select the desired color
Microsoft Excel MVP
&...Missing Import/Export Wizard
It just isn't there! There is no Import/Export option
listed under file menu in my outlook 2003. I have searched
everyplace I can think to remedy the situation and I've
found no solutions. I need to import some information into
my contacts, but without an import feature, I cannot do it.
Can anyone help?
Rerun Office Setup and do a full installation of Outlook and Office Tools
(run all from my computer).
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the discussion intact. Due to
the (insert latest virus name here) virus, all mail sent...Conditional Format Q
I wish to create a conditional format formula that will change the
background colour in a cell to Blue, if the value of another cell is
between to values (which are also detailed)
Cell I want to change is D8 on Sheet1
The dependent cell of D8 is in A8 on Sheet1
The two values which A8 must be between/or equal to is in F7 & H7 on
I just can't get my head around how to construct this
Select cell D8.
Format/ Conditional Formatting/ Formula Is/
=AND(A8>=Sheet2!F7,A8<=Sheet2!H7) if F7 is less than or equal to H7
Format/ Conditional Formatting/ For...conditional formula question
I have a spreadsheet which shows, in column A, several phsical location
of a variety of insect traps. In other words, I have replicates o
several trap types arranged by field location. In column B I have th
corresponding number of each trap type at each location. Is there a wa
to construct a formula which will return the sum of all of trap type 'x
from all locations. Here is an excerpt from the worksheet:
Hanley CM Study Traps
Biolure 1x 1
L2 (9008) 1
Biolure 10x 1
DA (8693) 2
Combo (9010) 2
DA20 (8997) 2
Da20/CM3 (9228) 2
Val...GroupByX Sum CounfOfX but show only the ones more than a given val
I want to be prompt to give a value for the Sum of CounOfX. For example
query should ask me to give a value and show me only the group sums equal or
more than that given value. I hope i could explain it.
SELECT qry_d2.Kunde, Sum(qry_d2.CountOfKunde) AS SummevonCountOfKunde
GROUP BY qry_d2.Kunde, Format$(qry_d2.Erfassungsdatum,' yyyy'),
HAVING (((qry_d2.Kunde)=IIf([Kunde] Is Not Null,[Kunde],"leer")) AND
((qry_d2.KundenNr)=IIf([KundenNr] Is Not Null,[KundenNr],"leer")));
Thanks you all very much in ...VBA code to paste data based on condition
I'm new to VBA programming and would appreciate some help with a macro.
There is a database table that can be refreshed throughout the month that
simply overrides the cell value from the same refresh. So the values update
every time you refresh in the same cell until the next month begins (Date +
Year are the column headings). What I need to do is break each month down
into weeks like this (1-7 = Week 1, and so on where Week 5 is any day after
the 28th of the month).
I need a macro where the morning a new week starts (Day 8), I can copy/paste
values the entire column in...AUTO SUM function returns 0.00 and formula displays =SUM()
After selecting a range of cells to sum, when I select the AUTO SUM function
it returns "=SUM()" in the last cell selected.
> After selecting a range of cells to sum, when I select the AUTO SUM function
> it returns "=SUM()" in the last cell selected.
Are the "numbers" actually numbers or are they formatted as text?
...conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have
a notifiction sent to you when a date on a spreadsheet has expired?
> how do you add a phrase to a field if the filed is blankl,
What "phrase" do you mean? A Comment? A value?
also, can you have
> a notifiction sent to you when a date on a spreadsheet has expired?
Maybe you can apply an open event (date to be tested being say in F1):
Private Sub Workbook_Open()
If Range("F1") < Date Then
MsgBox "Date expired"
...Conditional Format based on Month of Date in Cell
I have a cell that I typed in 1/1/2010 and I want to conditional format it
based on what month it is. Say format blue text if it's 1/1/2010 and green
text if 2/1/2010.
I did a test with formula =IF(MONTH(A1)=1,"yep","nope") and it returns "yep"
So I tried to use the following in the conditional formatting window based
MONTH(A1)=1 which does nothing.
The formula =MONTH(A1) returns a value of 1 so I don't see what the problem
Using formulas in conditional formatting, the equtation must always start
with an equ...Using multiple conditions in formula like countif(), sumif()
To some formulas like countif(); sumif(), we can type 1 condition to sort the
relevant data into use.
How about if I wish to use more than 1 condition?
I would like to set the following conditions when using formulas like countif()
- select the right rows (eg containing A/B/C or i/ii/iii)
- select the right range of numbers (eg:
--- 10-20 [inclusive] for values in A/B/C;
--- 35-45[inclusive] for values in i/ii/iii)
How to do?
- I'm using Office XP
- I'm using W...