Insert row if condition met
I need to insert a row after totaling several rows above it. The number of
rows vary, there are several spreadsheets and I update these spreadsheets a
couple of times a month. Is there way with a macro to do this and like to
bold my total line as well.
Inserting additional rows into your data is usually a bad thing.
How about an alternative.
You used Data|subtotal, right?
click on the outlining symbols to the left to just show the rows you want to be
Then select your rows in that range
click visible cells only
Adjust the rowheight for those...How can I count how many times different texts repeat in a column?
I have a column filled with text. there are about 100 cells in this column.
Those cells each have the name of a single corporation within them. many of
those corporation names are repeated throughout the column, but not all of
How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.
Microsoft Excel MVP
"pjr" <pjr@discus...XML <IF> condition
Following code segment looks good but the <IF> equates to true always and all
departments print... What am I doing wrong?
<ROW> "Non-Taxable Sales:" </ROW>
<SET name="NonTaxableTotal" type="vbCurrency"> 0 </SET>
<SET name="TATTOO" type="vbString"> "TATTOO" </SET>
<CONDITION> Report.Department.Name = TATTOO </CONDITION>
<ROW> Report.Department.Name &qu...conditional formatting
I'm not understanding conditional formatting re dates.
I have an Excel sheet which has a "Received Date" column. After three days
from the date entered in the "received date" column, I would like the text
for the text for the row to turn orange, after five days I would like the
text for the row to turn red +bold. After 7 days I would like for the rows
in red +bold to show turn yellow and a message box, on document start-up,
to show the user that these rows have overdue tasks that need attention
*now* (basically an irritating nag box)
My first concern is to get the co...conditional formatting #2
In my spreadsheet there are two colums with different
dates, i want to compare the two dates and if time period
is greater then 3 weeks then i want them highlighted.
If anyone can help it would be much appreciated
What I did to resolve this was to create a third column that subtracts the
dates. Then use this column and conditional formatting with a formula like
=$C$4>=21 to highlight the cells. In other words: first create a column that
subtracts the difference and then use conditional formatting to highlight
them if the difference is greater than 21 days.
Microsoft...Sum with multiple conditions
I have a source data sheet which has the following
Column E is the division
Column F represents the day of the month (ex: 1 thru 31)
Column G represents the month (ex 1 thru 12)
Column H represents the year (ex 2009 2010)
Column I represent s the sales revenue
Column N represents whether the sale went through (TRUE or FALSE)
In a different worksheet called results (same workbook)
Column A represents the Year ( 2009/ 2010)
Column B represents the month of the year (format Jan thru Dec)
Column C represents the day of the month (ex: 1 through 31)
Row 4, columns D thru S repre...Count totals with date
I need to count the total licences by type used weekly but display the
I think it should be something like:
=SUMPRODUCT((MONTH($C$2:$C$12)=1)*(YEAR($C$2:$C$12)=2004)) will count
the number of date entrys, but I dont know how to put the rest of it
Can any one help?
A B C
Licenses Totals Date
Web-mail 19392 01/03/2004
POP 19392 01/03/2004
Web-mail 99827 08/03/2004
POP 99827 08/03/2004
Web-mail 11023 15/03/2004
POP 11023 15/03/2004
Web-mail 10067 22/03/2004
POP 10067 22/03/2004
Web-mail 1...Skip condition if cell is blank
I have 4 conditions
1) If Sheets("Details").Range("H" & lngRow) > Date - 90 And _
2) Sheets("Details").Range("F" & lngRow) = Sheets("Search").Range("C2") And _
3) Sheets("Details").Range("J" & lngRow) = Sheets("Search").Range("C4") And _
4) Sheets("Details").Range("I" & lngRow) = Sheets("Search").Range("E4") Then
if Sheets("Search").Range("E2") is blank then condition 1 should be skipped
if Sheets("Sear...Conditional Format based on value in other cell
Hi! i have an issue where i want to conditionally format a cell (red,
yellow, or green) based on the value in another cell. For example, in
cell B20, the value will be 1 for green, 0 for yellow, -1 for red. I
want the value in that cell to change the color of cell B1, which
contains a complex formula combining two values from other cells (this
is a department expense analysis and that cell contains the variances
in $ and in % terms, so its hard to conditional format because of the
multiple values in the cell).
Can you help? Thanks in advance!
Instructions for XL2003 (XL 2007 is simila...Subform filter
I was a little embarrased to realize I know longer new how to do this... It's
been a while.
Anyway, I think what I'm after is fairly simple. I have a form and subform.
The subform has field call ExpirationDate. By default, i would like to Hide
any records where ExpirationDate < Today. However, I'd like to add a button
to the main for called "Display All" which would turn this filter off and
refresh the subform.
...Conditional formatting of date
I want to compare a date in a cell to the current date. If the current date
is past the date in the specified cell, and the adjacent cell is blank, I
want the specified date to display in red. Would someone be so kind as to
assist me with this please.
You need conditional formatting.
Select the cell to format, and let's assume that the compare cell is A1
Menu Format>Conditional Formatting
Change Condition1 to Formula Is
Add a formula of =AND(A1<TODAY(),B1="")
Choose the text colour
"cebubum" <cebubum@discussio...Using Dates in Conditional Formatting
How can I get the date in cells in column K to turn red if the dat
entered is more than (ie later than - but not equal to) the dat
already in column J ? AND
If I do that, how can I total the red occurances at the end of colum
Message posted from http://www.ExcelForum.com
try the following:
- select for example K1
- goto 'Format - Conditional Format' and enter the following formula
to count these don't use the color (which is difficult for conditional
format colors) but use a formula like
Frankf...COUNTING DATES #4
How do i write a formula to count the number of lines on a database of
information that have a date that matches todays date? Say I have a huge
database with 10,000 lines of info and I want it to look at all the info and
put a number in a cell that says how many of the lines in the datbase contain
See COUNTIF Worksheet Function in help
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Cody" &l...Count if
I trying to count enties with different conditions
if this A1=A2,1, if A1<>A2,0, if A2<>A3,1
can somebody help me setting up this formula
You might want to take a look at the OR/AND functions (XL help file). For
what you have so far:
*Remember to click "yes" if this post helped you!*
> I trying to count enties with different conditions
> if this A1=A2,1, if A1<>A2,0, if A2<>A3,1
> can somebody help me setting up this formula
&g...Conditional Formatting Row
Operating System: Mac OS X 10.6 (Snow Leopard)
I created a spreadsheet as a todo list. When the task becomes overdue, the entire row turns red. When a task is completed, it applies strike-through to the entire row. Last night the everything was working perfect. <br><br>However today, when a task becomes over due, only 3 of the six rows turn red. I thought I might have made a formula error, but when I click on the cells still in white, THEY TURN RED!! (same story with completed tasks and strikethrough format). <br><br>So now the condit...Counting against a date
I want to count the rows in a range 'Tests 1' AF3:AF1000=YES if the date
in the range 'Tests 1' AD3:AD1000 is (<=) less than or equal to the date
in B14 (current sheet) using the following formula:
unfortunately this returns an #N/A error
I'm clearly missing something here so any tips or pointers would be
Sometimes I think we're alone. Sometimes I think we're not. In either
case, th...Duplicate Detection Count
Is there a way to add a count onto the duplicate detection job results grid
to show how many duplicates were found in each job?
...Sum of counted values in a query
I have a query which count the number of records in a table (as a
result of the menuoption view-> totals ->count) as a result of the
group by function for unique records.
The result is:
Now I want to insert a column3 which gives the sum of that counted
values. I'd tried something like: expr1:Sum(Count[column2])
The result should be for each record in column3: 33 (result of the
It doesn't work.
Somebody knows a solution ?
You can always writ...conditional counting #4
Here is my data (in actuality, it's about 35,000 rows long and will be
growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled
as "Format 1".
TYPE (column A) PERIOD (column B) VERSION (column C)
DS 02/2005 1
BS 02/2005 2
WS 03/2005 2
DS 02/2005 1
I want to be able to count all of the different combinations that occur
(i.e., count all of the "DS and 02/2005 ...Record Count in Query
I have a query that I'm having trouble in figuring out. I have created a
database that is used for tracking sewer overflows at certain points in the
city. The points in the city are labeled with a number, (i.e. 102, 103,
104,...up to 130). These points are checked roughly 2-3 times per week and
when they are checked I have a simple form that is filled out by the user.
It has a few fields, one is date, one is point number, and another is "Did
overflow occur?" (Yes/No field). I am trying to make a query that will list
each point number once, and then count the number of ...editing conditional formating formulas
I am wondering if it is possible to replace text of a CF formula with new
text given by the user through an input box .
I need it to do this throughout every sheets' CF formulas within a workbook
and every time a new customer is added to the spreadsheet.
I have already made the CF formulas for new customers by entering newa,
newb, newc......newp which need to be replaced with customer names. And I
have already set the range on which to apply the CF rules.
It is unnecessarily complicated. Use a CF formula that doesn't need to be changed, one that
r...Counting only user defined cells if it contains a string (# & text
I'd like to thank everyone in advance for replying to this question.
Hi, I'd like to get an assistance in resolving this problem. Im tryin to
find out how could I count a user defined cells (6 rows) containing a string
(ex. 2435 Smith Dr) using a formula. Currently, I'm just entering the count
manually and I know there's a solution how to automate the counting. If the
cell is empty the value shold be returned for that cell must be 0 and if its
contain the string, its value must be 1..This is how I'm approacing it but it
doesn't work using the IF function....Thank...Setting a conditional value in a query to perform an additional qu
Ok, I have a slight brain twister here and wondering if anyone could help
shed some light on this. :)
Im in the midst of creating a database that will hopefully replace a
paper-based system of storing 'shift runsheets' from a 24/7 office. There is
3 shifts per 24 hours; Morning (6am-2:30pm), Afternoon (2pm-10:30pm) and
Night (10pm to 6:30am). Each day it adds a new row of data to the table
"shift" and users can peform searches on previous sheets by searching the
table "previous" which links to "shift". When a user wishes to search, they
select the ...If Statement with Two Conditions
I'm trying to do an if statement with two conditions like the following
If a value falls between two dates then give it a value of 1, if not give it a value of 0. The if statement works well with one condition but when I try to do something like this = if (a2<=b2<=c2,1,0) it does not work. Help
Try something like
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Leroy" <firstname.lastname@example.org> wrote in message
news:EF57802D-CA6F-4A45-95FB-9807FFEA1...Count # of records in a table..
Hi, I just need a simple chunk of code that returns the total number of
records on a given table without any filtering...sounds easy enough i
Simplest thing is
that works for local tables and linked tables.
If the tables are not linked but are in the database, this is probably the
fastest way to get a count.
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Steve P wrote: