Free Microsoft Excel 2013 Quick Reference

Conditional format on calculate Results

Hey all! I'm a beginner in Excel and I need some help. I'm able to do light calculations using codes, (b5+c5=) and similar but nothing as complex as the task I'm going to present here.

In short: I need to calculate something that is not a simple "1+1=2", but more along the lines of "1+1+1=1"

Long story: I train at a local martial arts dojo and we get goverment funding for every person under the age of 20. (The funding is a way to encourage sports & activities for youngsters here in Sweden). In the dojo it is my job to compile and send the Attendance Reports which will show the number of individual "gatherings" that warrant these fundings from the goverment. I do this by hand at the end of each semester and it is tedious to say the least.
As mentioned it is not a simple matter of adding "1+1+1+1= no problem".

We get funding through the following arrangement: On any one single training session there has to be at least 3 students under (or at) the age of 20 for us to be able to seek funding. We call that a "gathering". The higher the number of legit "gatherings", (with 3 or more individuals at or under the age of 20), the more funding we get. So I have to view the attendance papers manually for each training session and pinpoint who was at the training, what age he/she is and then write down the results. I do that for each training session of the semester.

I have a blank attendance report in XLS format and I wish to create a way to automate some or all of the above mentioned process.
(For reference, here is a link to one of them

I dont know the technical terms, but I want to be able to create a macro/function/whatever that scans the date of birth, the attendance date, and IF there are more than 3 legit under 20 yrs old students in one training session an addition will be made (+1) to a "gathering" figure.

Is this feasable? Can anyone of you point me in the direction of how to do this?

All the best
/Fred

I have made a simple SPC program for the company I work. I am not a computer expert but I manage to make a statistical process control charts. All it does that you have 26 columns where you have to put 5 values in each column. Then it calculates the mean, range and standard deviation. There are many different sheets in a workbook basically one for each different manufacturing process. Each sheet has different control limits. I have used conditional formatting so that when ever the value is outside the control limit it turns red. And if the average of 5 values is outside the limit operator has to mention the error code which are from 1 to 9 (which they are not doing it). I want to develop something like that when ever the average of the five values is outside the control limit the operator cannot enter further data unless they put an action code first. I have two ideas how to do it but can’t make a macro as I am not very good in programming.

i) First of all I thought that when ever the average value turns red (out of control) this triggers the macro and they can’t do anything except for entering the fault code first. And once they have entered the fault code value they can carry on as normal. There is one problem with this that the average is going to turn red suppose the first value is out of limit. But I really want to trigger the macro once we have put all the 5 values in.

ii) Secondly, I was thinking to put the control limits for each chart on specific sheet and the macro takes the values from those cells and assign a variable say x and y to them. And so we can use if then else loop and say that if the values are within these limits everything is fine and if the values are outside x and y, the user cannot enter any data unless he put an action code first.

I hope I have explained my self as clearly as possible. I am really stuck and its very urgent for me. Hope to hear from some one really soon.

All,

I want to use the following worksheet calculation
within a VBA macro.

The idea is that if today's date falls within the FromDate and ToDate, the text "Unavailable" is written into a cell, so that i can apply conditional formatting onto a person's name.

As an example, I am on holiday from 8th March 2010 (FromDate) to the 12th March 2010 (ToDate). Because today is the 11th, the cell should say that I'm "Unavailable".

Does anyone know how to write this in VBA code?

Thanks

I'm using Excel 2002

I have a SheetSelectionChange event that stores the Target.Row in a cell on the active worksheet.
All rows in a column use conditional formatting to highlight the cell if the row = stored row #
Works fine.

Problem:
There are thousands of formulas in the workbook.
It takes the line
 at least 1 second to execute.
I have turned off enableevents, before that line.
I discovered that setting calculation = manual speeds it up, but then when I set calculation = automatic, it takes at least 1 second to calculate everything. There are no formulas that refer to .Range("Selection_Row"), so there are no calculations. Only the conditional formatting refer to it.

Getting tired of waiting for Excel to catch up with my typing. Any way to speed it up?

Hello All,
On another thread I had my problems solved for getting multiple conditional formats to work, and it DOES work!

However data now takes far too long to enter; for example if I type the number 9 into a cell it takes around 30 seconds to let me enter more data, it is as if it is working on some calculations. The VBA I'm using is as shown below. Any help with this would be greatly appreciated.

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim Cell As Range
Dim Rng1 As Range
     
    On Error Resume Next
    Set Rng1 = Union(Range("A:A"), Range("C:C")).SpecialCells(xlCellTypeFormulas, 1)
    On Error GoTo 0
    If Rng1 Is Nothing Then
        Set Rng1 = Union(Range("A:A"), Range("C:C"))
        Else
        Set Rng1 = Union(Range("A:A"), Range("C:C"))
    End If
    For Each Cell In Rng1
        Select Case Cell.Value
            Case vbNullString
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
            Case "Tom", "Joe", "Paul"
                Cell.Interior.ColorIndex = 3
                Cell.Font.Bold = True
            Case "Smith", "Jones"
                Cell.Interior.ColorIndex = 4
                Cell.Font.Bold = True
            Case 1, 3, 7, 9
                Cell.Interior.ColorIndex = 5
                Cell.Font.Bold = True
            Case 10 To 25
                Cell.Interior.ColorIndex = 6
                Cell.Font.Bold = True
            Case 26 To 99
                Cell.Interior.ColorIndex = 7
                Cell.Font.Bold = True
            Case Else
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
        End Select
    Next
 
End Sub

The original VBA, before I specified cells was as follows, this works without any delay...I'm not very good at all this stuff. Please help!

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim Cell As Range
Dim Rng1 As Range
     
    On Error Resume Next
    Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
    On Error GoTo 0
    If Rng1 Is Nothing Then
        Set Rng1 = Range(Target.Address)
        Else
        Set Rng1 = Union(Range(Target.Address), Rng1)
    End If
    For Each Cell In Rng1
        Select Case Cell.Value
            Case vbNullString
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
            Case "Tom", "Joe", "Paul"
                Cell.Interior.ColorIndex = 3
                Cell.Font.Bold = True
            Case "Smith", "Jones"
                Cell.Interior.ColorIndex = 4
                Cell.Font.Bold = True
            Case 1, 3, 7, 9
                Cell.Interior.ColorIndex = 5
                Cell.Font.Bold = True
            Case 10 To 25
                Cell.Interior.ColorIndex = 6
                Cell.Font.Bold = True
            Case 26 To 99
                Cell.Interior.ColorIndex = 7
                Cell.Font.Bold = True
            Case Else
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
        End Select
    Next
 
End Sub


Basically, I am trying to create timelines for a project with each row
indicating an element of work.
So for example, in Row 6 Col A, I enter a text description, Col B would

have a number input (from 1 to 10 or larger), Col C would require an
inputed start date, Col D an inputed end date, Col E some other
information and starting from Col F to the end of the row (Col IV) -
i.e. sequential dates - I need to be able to see the time line, the
colour of which would depend on the number inputed in Col B.
Example, Col A would be "Initiate project", Col B "1", Col C
"3/6/2006", Col D "5/6/2006" and Col E has a formula calculating the
number of days between start and end dates. Then if I started the time
line from the 1/6/2006, the cells Col F & G would be blank, those in
Col's H to J would be solid colour (colour depending on the input in
Col B) and from Col K to Col IV also blank.
I need to repeat the timeline from row's 2 downwards.
I hope my description of the spreadsheet is a bit clearer than mud!!
Can you help?
markvdh

I am an intermediate user of Excel and VBA. I have a problem where
exported worksheets are transferred and then printed out. The rows are
color coded as to what to do if a row is a certain color. However we
don't use/have a color printer, it does give different shades of
grey, but is not all that discernable on a printout.

I took an original DAVID MCRITCHIE routine and modified it. (Thank you
very much Mr. McRitchie J)

Sub ChangeFontPerColorindexOfColS()
'Commented out UPPER CASE sections are from Original which precedes
areas I needed to subsitute.
'Modified David McRitchie Sub Originally {Sub DeleteRowsRedIncolA() }
'DAVID MCRITCHIE 2002-01-17
' HTTP://WWW.MVPS.ORG/DMCRITCHIE/EXCEL/COLORS.HTM
'WILL NOT FIND COLOR DUE TO CONDITIONAL FORMATTING
'Again, Thank you very much Mr. McRitchie J
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim rng As Range, ix As Long
' SET RNG = INTERSECT(RANGE("A:A"), ACTIVESHEET.USEDRANGE)
Set rng = Intersect(Range("S:S"), ActiveSheet.UsedRange)
For ix = rng.Count To 1 Step -1
If rng.Item(ix).Interior.ColorIndex = 3 Then
rng.Item(ix).Font.Bold = True
' rng.Item(ix).EntireRow.Delete
With rng.Item(ix).Font
.Name = "Arial"
.Size = 12
End With
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

The above modified David McRitchie Sub originally {Sub Delete
RowsRedInColA() } will change the font and size of all rows at the
intersect(Column S).
My question is, how can I modify this to change font of certain cells
in the row(s) when ColorIndex of Column S changes.
IE:
If the colorindex of S is 3 (red), I want to change the font to bold
and size to 12, for columns A thru R,in that row.
If the colorindex of S is 7(purple) I want to change the font to bold
and size 12, for columns A & B in that row.
If the colorindex of S is 8(ltblue) I want to change the font to bold
for column A in that row.

I have tried so many different combinations (offsets, ranges, etc),
there are too many to list here. I am embarrassed to say also I have
been working on this for months. I tried changing it to select cells
per the color index, and it appears to stall after changing/selecting
only the first row. It seems I am not declaring something or I need an
array (which I don't understand all that well), or.... . I dunno. I
am using this as a tool to learn stepping thru data and IF statements.
I also tried Select Case scenarios to no avail. If I can get a clear
cut reason as to why the routine stops after the first iteration, it
may give me some good insight as to logical syntax in IF and Select
Case statements. I hope Mr. McRitchie is ok with me using his stuff.

Problems are just opportunities for achievements, to someone.

Hi guys,

Is it possible to change the colour of a tab based on the value of one or many values in a set of cells in one column.

I have column Z, rows 4-86 set to calculate figures entered in other cells, If any of the numbers in Z4 - Z86 go over 0 I have a conditional format set to turn that particular cell red.

What I would like is also for the tab colur of the worksheet to change to red as well if any of these cells are over 0

Is this possible?

Many thanks

Paul

See the attached sheet. I am struggling with how to do this. I need the SUMPRODUCT to calculate accordingly. Maybe a mix of SUMPRODUCT, OFFSET, and ISBLANK would solve this, but I am not smart enough to figure it out.

The SUMPRODUCT needs to only sum the italicized rows by Resource Group (Column E), but filter by Community and Type in Columns B and C. Problem is that these data sets are not inline with the italicized lines (they are usually above a couple rows depending on how many resources are on the project).

Also, note that conditional formatting is using ISBLANK to make the bold and italics.

Any idea how I can pull this off. Let me know if I need to explain this better.

Hi
My data sheet looks like this, (please see the attached sheet)

Problem:
I want to calculate AVERAGE workdays (number of days between “Date & Time Raised” and “Date & Time Closed”), based on following conditions,

Conditions:
1) Severity = “Prod Severity 4” OR “Development Severity 4”
2) Status = “Closed”
3) Month Closed = e.g. “Oct-2008”
4) Ignore errors (e.g. #VALUE!) while taking average of elapsed day

This datasheet is a dump from another database, the columns “Date & Time Raised” and “Date & Time Closed” may not be in date format. I use ‘dd/mm/yyyy’ format. I would like to ignore the time stamps while computing workdays.

Unable to insert any new columns to compute part-by-part. Please suggest a single cell formula which can be entered. An array formula taking all the above conditions would be great.

Thanks in advance.

I am creating a parts list and I want to be able to enter values into a cell and have them formatted in engineering format with engineering labels. I have made a custom format for resistor values so that when you enter 1600 it formats it to 1.6 k and when you enter 1000000 it formats it to 1.0 M. This is important so that if the parts were to be sorted by value, they would be in the correct order. If you were to just enter 1.6 k and 1.0 M and 1.0 k, then sorting them would make them 1.0 k 1.0 M and 1.6 k which is not correct. Now I would like to do the same but for capacitors, which are normally decimal values <1 . For example, 0.000001 would be 1 u and 0.001 would be 1 m and so on. Using the engineering notation format ##0.0E+0 does this, but I want the E+0 to be replaced by the symbol that represents the SI unit. Is there a way to use the custom formatting to do the engineering notation calculation but hide the E+0 part? Or is there some other way to make the custom formatting manipulate the decimal place? I have the conditional part of the custom format so that it recognizes what symbol to use. Any ideas?

Here is the custom format code I used for the resistor values.
[>999999]##0.00?,,"MΩ";[>999]##0.00?," kΩ";##0.00??" Ω"
this code lines up the decimal points as long as the cells are right aligned
Here is what I have for the conditional part for capacitor values. The 0.00 part is what needs to be changed.
[<0.000000001]0.00" pF";[<0.000001]0.00" nF";0.00" µF"

Greetings. My first post here.
I am a commercial pilot in Alaska. I am attempting to create a spreadsheet to track training/checkride due dates for our group of pilots. Here is how I would like to go about this. First, let me give you a bit of information about how the FAA tracks these events. In simplistic terms, if an event takes place on any day of a month, it comes due in any day of the sixth (or 12th month) following the month it was accomplished in. Still with me? I would like to enter the day the event took place in a cell (Let's say 4 July 2006). This event would come due on any day in Jan 2007. I would like for that cell containing the accomplished date to turn red on the first day of January 2007, and remain red for the entire month. I can use conditional formatting to make it turn red beginning on any number of days after the event; however, I would like to have Excel be able to calculate the beginning date of the month in which the event took place and add the appropriate amout of time (6 months or 12 months) so that the cell turns red on the first day of the due month.
Does this sound feasible? Is conditional formatting possible in this situation?
What would be a better approach to pursue?
Thanks,
Jerry in Anchorage

I have an Excel spreadsheet which I would describe as non tirivial but not
too elaborate either that runs fine in Excel 2002 but becomes unbearably slow
doing the simplest of tasks in 2003. Example, opening / closing row groups
can take 30-90 seconds or more in 2003 versus <2 seconds in 2002 for the same
exact spreadsheet. This is a master template that we teach many others to
use so it must run on all machines running 2002 and 2003 - besides newer
Office versions are supposed to be faster not slower RIGHT? So please HELP !

Background...
The bulk of the spreadsheet is in a single worksheet that is 60 columns x
2000 rows (~4MB total file size) . There are some OFFSET functions located
within the main spreadsheet and a few on a secondary sheet. I also have cell
drop down menus and conditional formatting used sporadically throughout (~100
cell menus / column and maybe the same for conditional formatting). I have
the problem in Excel 2003 with a version that has simple GoTo macros or
doesn't have macros on, enabled, disabled or deleted entirely. I refuse to
turn Auto Calculation off because that is asking for trouble with people
using bad results. HELP !

I would like 2 apologize to everyone who have help me on the post of
"Counting Cells" but im confused with everyone agreeing and disagreeing on
using this and that methods...which i don know where to make ammendments in
my code and formulas now....Thanks to u all first....

Could someone pls help me clearly on how should i do it?
My database contains some coloured cells.....with conditional
formatting.....and i would like to have maybe the calculation below
.......which will calculate how many E.g. columns in red
columns in orange
columns in yellow
and etc........

Its urgent and i would be so glad if anyone can help me step by step
and clearly on this? Im not really good in Excel....that's y im trying to
learn..... Thanks to all.....

Hey folks,

My dilemma:

I maintain orders and i need to verify if the quantity ordered is in the correct pack quantity. I have a spreadsheet that i use to keep all of the daily order/ship quantities. i would like to be able to enter the requested ship quantity and with out calculation/figuring on my end see if the number is in fact in the correct pack quantity. For example:

Part Number 123 has a pack qty of 40. Customer requested 65, i would like to be able to see this number (65) in Red, or some way of making it stand out.

If "conditional" formatting only have "divisible by" available, life woudl be easy.

Please see the attachd for an "finished" example of what im looking for.

Any help would be GREATLY appreciated.

Thanks alot!

Nick

I'm comparing data between two worksheets on a monthly basis. The data has an ID number for each item in a particular row.

Each month there may be new data in a row or data may be deleted in a particular row. The goal is to add the data from the newest worksheet into the existing worksheet and show whether there are new ID's or deleted ID's from the newest worksheet versus the former.

The need is to ensure that the data or rows are lined up properly for calculation purposes.

Is there any way of using a function or some other means for aligning the data between the two worksheets. The conditional formatting function does not appear to be the most pragmatic option.

Thanks in advance.

I have two worksheets with ranges, one is 452 rows by 11 columns and the other is 152 rows by 28 columns. The ranges are not only filled with data but make heavy usage of conditional formatting.

Each day, I need to shift all of the rows down one row. Doing a copy/paste of the entire range less the first row blows up on memory usage.

I wrote the following VBA routine to handle the shift one row at a time ... this one handles the 452x11. It does the job but takes 20 seconds on the 452x11 range and same routine takes 40 seconds on the 152x28 range.

  Dim iRangeRowX As
Integer
  Dim iRangeRowsInRange As Integer
  Dim iRangeBeginsRow As Integer
  Dim iRangeEndsRow As Integer
  Dim rRange As Range
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Worksheets("BreadthModels").Activate
  Set rRange = Worksheets("BreadthModels").Range("BreadthModelTable")
  With rRange
    iRangeRowsInRange = .Rows.Count  ' row count for this range
    iRangeBeginsRow = .Row     ' first row for this range
    iRangeEndsRow = (iRangeBeginsRow + iRangeRowsInRange) - 1
    ' columns are hard coded
    For iRangeRowX = (iRangeBeginsRow + 2) To (iRangeEndsRow - 1) Step 1
      Range("E" & iRangeRowX & ":P" & iRangeRowX).Select
      Application.CutCopyMode = False
      Selection.Copy
      Range("E" & iRangeRowX - 1 & ":P" & iRangeRowX - 1).Select
      ActiveSheet.Paste
      DoEvents  'allow break
    Next
  End With
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Calculate
Is there anything which can be done to speed execution? Thank you.

Earl

I am struggling as a novice to get certain cells to populate only after others do. In my example the top row starts with the total for the year and then goes on to Jan through Dec. These are predetermined budget numbers. I'd like to compare not only month by month as I add data for January and so on as the year goes on, but also as a cummulative YTD total. The second row would be the same top in format but as I said, data would only be added as and when (end of Jan, end of Feb., etc.) and the total cell would populate from there.

Hi,

am new here,

have read a couple of threads which seem to start to discuss what I am after doing for instance http://www.excelforum.com/excel-prog...csv-files.html but instead of my hijacking this thread in particular I would like to describe what I hope to achieve here and hopefully you guys can say,

"yeah you could do this, that and the other".
or
"Nah, that aint gonna work and this is why...."

Long and short, I work as a Techie within a small IT firm, I need to present in a timely fashion the total file space used by each type of file (by file extension) on a file server into a nice readable report.

I am able to output the fileservers usage into a CSV file, scheduled on a nightly/weekly/monthly, at the moment I run this just on a weekly basis.

This manual process is carried out on a weekly basis,
I import the csv data into a new excel spreadsheet, I then sort the output into (byte) size order, I then add a column which is a calculation which multiplies the byte count by 1,073,741,824 to give me GB I then format this formula column to 2 decimal places. I then disgard the bottom 3900+ rows of data leaving a top 100 rows which includes all the "usual suspect" file extensions, including Doc, Jpg, etc
I then hide some columns and print my findings out.

I wish to improve this by automating all of the formatting, adding some trend analysis by looking at previous weeks data and also allowing other members of staff with limited knowledge to carry this task out when I am away on leave or training courses.

I have started by adding a button to a spreadsheet which allows me to import a standard CSV file into a new sheet which is renamed with todays date.
as per http://www.mrexcel.com/articles/macr...ily-report.php

what I am stuck with now is how to format the page and then do some conditional calculations when sheets don't exist (yet).

my apologies for long winded posting....especially as it's my first posting!!

regards

t,

Hi All,
I am working on a risk register and have created some VBA formula to colour the cells dependent on the level of risk (I have 5 levels of risk therefore need VBA as opposed to conditional formatting). The cells that contain the level of risk contain formula that are dependant on other "Input"cells - specifically I have Column A (titled "Probability"), it has a drop down list to choose from; and column B (titled "Impact") another drop down list to choose from; column C contains a formula that has an INDEX & MATCH formula to calculate the score from columns A and B (looking up a separate table); then column D contains the INDEX and MATCH formula that returns the Risk Level dependant on column C's score (ie the one I want the colour to change in. The code I have put in works in the first instance when a risk is first entered and the risk is calculated from the "Input" cells (A&B). However, when the "input" cells are changed (hence change the level of risk in the risk cells), the formula works OK to change the formula result (eg "Critical" changes to "Severe"), but the cell colour does not automatically update with it! It only works if I double click the cell. Does anyone know how this can automatically update?
Cheers
Jay
The following is the codecode:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "Critical Risk"
oCell.Interior.ColorIndex = 3
Sheets("Risks").Range(oCell.Address).Interior.ColorIndex = 3
Case Is = "Severe Risk"
oCell.Interior.ColorIndex = 46
Sheets("Risks").Range(oCell.Address).Interior.ColorIndex = 46
Case Is = "Significant Risk"
oCell.Interior.ColorIndex = 44
Sheets("Risks").Range(oCell.Address).Interior.ColorIndex = 44
Case Is = "Minor Risk"
oCell.Interior.ColorIndex = 6
Sheets("Risks").Range(oCell.Address).Interior.ColorIndex = 6
Case Is = "Possible Concern"
oCell.Interior.ColorIndex = 4
Sheets("Risks").Range(oCell.Address).Interior.ColorIndex = 4
Case Is = "No Risk"
oCell.Interior.ColorIndex = 2
Sheets("Risks").Range(oCell.Address).Interior.ColorIndex = 2
Case Else
oCell.Interior.ColorIndex = xlNone
Sheets("Risks").Range(oCell.Address).Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub