I am going to explain how to create the highlighting and the conditional formatting formulas behind.

Setting up the conditional formatting

1. Select cell range A5:D25
2. Go to tab "Home"
3. Click "Conditional formatting" button
4. Click "New Rule.."
5. Click "Use formula to determine which cells to format"
   
6. Type conditional formatting formula
7. Click "Format..." button
8. Select a color
9. Click OK
10. Click OK

Repeat above steps and create new conditional formatting rules with these formulas and colors:

and

Explaining conditional formatting formulas

It is important to understand how relative and absolute cell references work. Remember that cell range A5:D25 was selected before you created the conditional formatting rules.

The first conditional formatting formula:

Step 1 - Highlight cells that have a column header equal to the value in $C$2.

(A$5=$C$2)

The cell reference A$5 changes in each cell in cell range A5:D25 but remember only the column reference changes. This makes the formula comparing only the corresponding column header.

Example,

In cell B7, the cell reference changes to:

(B$5=$C$2)

becomes

"Company Name"="First Name"

and returns FALSE.

Step 2 - Highlight cells that have a Region value equal to the value in $C$3

($A5=$C$3)

The cell reference $A5 changes in each cell in cell range A5:D25, only the row reference changes.

Example,

In cell B7, the cell reference changes to:

($A7=$C$3)

becomes

"Europe"="Africa"

and returns FALSE.

Step 3 - Check if both criteria are TRUE

=(A$5=$C$2)*($A5=$C$3)

becomes

=FALSE*FALSE

and returns 0. Cell B7 is not highlighted with the selected color.

Recommended links

You can do a lot of interesting stuff with conditional formatting. You can search for cell values containing text strings or highlight records in a list. You can find many more examples in the conditional formatting category.

Download excel 2007 *.xlsx file

Search with conditional formatting.xlsx

Related posts:

1. Highlight smallest duplicate value in a column using conditional formatting in excel
2. Highlight the second or more duplicates in two lists using conditional formatting in excel
3. Highlight dates within a date range using conditional formatting
4. Highlight duplicate values in a range using conditional formatting in excel
5. Highlight duplicates using conditional formatting in excel
6. Excel – Search/Lookup

hello Oscar, i am building a spreadshet for tracking calls for my local fire depatrment. i have column "a" as incident number. the incident number is a one timme yearly number usage. column "c" is apparatus name and there are 1 of 8 possible names may be used in this cell. column "h" has the formula to give me the time spent on scene. i am needing help getting sheet 2 to tag the time spent on a call per apparatus. sheet 2 is names of personnel on scene. i want to put the time on scene according to what apparatus they were on for each incident.

ecample:
column "a" newest entry is #10
column "c" is "bt1" or "bt2" or "e1" or "e3" or "e4" or "e5" or "pov" or "stby"
there often will be multiple rows with the same incident# in column "a" but differant apparatus in column "c".
Column "h" will have on scene time calculated by "=f5-d5"(for that row)
i need to tag the on sceen time from sheet 1 column "h" to the corrisponding incident number column "a" according to the apparatus column "c".
Last Total
Enroute Arrival Clear Response Incident
Incident # Date Apparatus Time Time Time Time Time
1 03/01/12 bt2 8:18 8:27 18:45 0:09:00 10:27:00
2 03/25/12 bt2 8:20 8:23 17:45 0:03:00 9:25:00
e1 17:05 17:10 17:45 0:05:00 0:40:00
e3 12:33 12:38 17:45 0:05:00 5:12:00
3 03/26/12 e4 7:45 8:08 10:22 0:23:00 2:37:00
4 03/26/12 bt2 11:14 11:16 11:29 0:02:00 0:15:00
5 03/27/12 pov 13:10 13:20 18:36 0:10:00 5:26:00
stby 13:15 13:20 18:36 0:05:00 5:21:00
bt1 13:15 13:20 18:36 0:05:00 5:21:00
bt2 13:16 13:21 18:36 0:05:00 5:20:00
6 03/28/12 e1 8:18 8:27 18:45 0:09:00 10:27:00
e3 8:20 8:30 18:45 0:10:00 10:25:00
7 03/28/12 bt1 8:20 8:23 17:45 0:03:00 9:25:00
e5 9:00 9:03 17:45 0:03:00 8:45:00
8 03/28/12 bt2 9:20 9:22 9:59 0:02:00 0:39:00
9 03/29/12 e1 17:45 17:50 18:00 0:05:00 0:15:00

Answer:

Here is your data presented in sheet1:

The array formula reads incident numbers and apparatus values therefore I entered missing incident numbers.

How to enter a single formula in all blank cells in a cell range

1. Select cell range A1:A17
2. Press F5
3. Click "Special"
4. Click "Blanks"
5. Click "OK"
6. Type =A3
7. Press and hold Ctrl
8. Press Enter

Array formula in sheet2

Array formula in cell C2:

How to enter an array formula

1. Double click cell C2
2. Copy/Paste array formula
3. Press and hold Ctrl and Shift
4.  Press Enter

How to copy array formula

1. Select cell C2
2. Copy (Ctrl + c)
3. Select cell range C3:C10
4. Paste (Ctrl + v)

Explaining array formula in cell C2

Step 1 - Find incident number and Apparatus value

(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17)

becomes

("bt2"={"bt2";"bt2";"e1";"e3";"e4";"bt2";"pov";"stby";"bt1";"bt2";"e1";"e3";"bt1";"e5";"bt2";"e1"} ) * (1={1;2;2;2;3;4;5;5;5;5;6;6;7;7;8;9})

becomes

{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

Step 2 - Convert array to row numbers

IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")

becomes

IF({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")

becomes

IF({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}, "")

and returns

{1;"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

Step 3 - Find smallest value in array

MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), ""))

becomes

MIN({1;"";"";"";"";"";"";"";"";"";"";"";"";"";"";""})

and returns

1

Step 4 - Return an error if no value is found

IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), "")))

becomes

IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), 1))

becomes

IF(SUM({1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})=0, NA(), 1))

becomes

IF(1=0, NA(), 1))

becomes

IF(False, NA(), 1))

and returns 1

Step 5 - Return time value

=INDEX(Sheet1!$H$2:$H$17, IF(SUM(--(B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17))=0, NA(), MIN(IF((B2=Sheet1!$C$2:$C$17)*(A2=Sheet1!$A$2:$A$17), MATCH(ROW(Sheet1!$A$2:$A$17), ROW(Sheet1!$A$2:$A$17)), ""))))

becomes

=INDEX(Sheet1!$H$2:$H$17, 1)

becomes

=INDEX({0,435416666666667; 0,392361111111111; 0,0277777777777778; 0,216666666666667; 0,109027777777778; 0,0104166666666667; 0,226388888888889; 0,222916666666667; 0,222916666666667; 0,222222222222222; 0,435416666666667; 0,434027777777778; 0,392361111111111; 0,364583333333333; 0,0270833333333333; 0,0104166666666667}, 1)

and returns 0,435416666666667 in cell C2. Formatted as time, 10:27:00

Download excel 2007 *.xlsx file

Bill Truax.xlsx

Related posts:

1. Tracking a stock portfolio in excel (auto update)
2. Tracking a stock portfolio #2 in excel

How it´s done?

• Conditional Formatting
• Array formulas
• Dynamic named ranges
• No vba

Download template excel 2007 *.xlsx

school schedule.xlsx

Related posts:

1. Weekly schedule template in excel
2. Setting up your work hours in a weekly schedule in excel
3. Schedule recurring events in a weekly schedule in excel
4. Highlight specific time ranges in a weekly schedule in excel
5. Excel template: School attendance register
6. Excel school
7. Microsoft Excel

Example 1,

This example code copies cell range A1:B2 to A5:B6 in the active sheet. You can make the code even shorter: Range("A1:B2").Copy Range("A5").

Example 2,

The current region is a cell range surrounded by blank rows and columns. This is equivalent to Ctrl + a.

This works very well if the cell range doesn´t have blank rows or columns.

Example 3,

Cell range A1:B6 has blank cells in row 4.

Example 4,

This cell range (A1:B6) has a blank row (4) and a blank cell (A6).

Download excel 2007 *.xlsm file

copying techniques.xlsm

Functions:

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

VBA:

Worksheet Range Property

Worksheet Rows Property

Range.CurrentRegion Property

End Property 

Related posts:

1. Copy selected rows (checkboxes) (2/2)
2. Change chart data range using a drop down list (vba)
3. Excel vba: Select cell A1 on all sheets before you close a workbook
4. Excel vba: Filter a table using the selection change event
5. Change pivot table data source using a drop down list

I need to identify the duplicates based on the Columns D:H and put in Column C a small “d” + a running number for the duplicates which are duplicated the same.
If no duplicates only to put in a “d0” for instance.

Answer

Array formula in cell B3:

How to create an array formula

1. Double click cell B3
2. Copy/Paste above formula
3. Press and hold Ctrl + Shift
4. Press Enter

How to copy array formula

1. Select cell B3
2. Copy (Ctrl + c)
3. Select cell range B4:B11
4. Paste (Ctrl + v)

Download excel 2007 *.xlsx file

Group duplicates.xlsx

Related posts:

1. Identify duplicate files in excel
2. Filter duplicate rows in excel 2007
3. Highlight duplicate rows in excel 2007
4. Find duplicate records in excel 2007
5. Identify duplicate invoice records in excel

Example,

Macro code

Sub ListWorkbooks()
Dim wb As Workbook
Dim ws As Worksheet
Dim i, j As Single
Set ws = Sheets.Add
For j = 1 To Workbooks.Count
    Range("A1").Cells(j, 1) = Workbooks(j).Name
    For i = 1 To Workbooks(j).Sheets.Count
            Range("A1").Cells(j, i + 1) = Workbooks(j).Sheets(i).Name
    Next i
Next j
End Sub

How to create macro

1. Open VB Editor, press Alt+F11.
2. Right click on your workbook in project explorer.
3. Click "Insert" and "Module".
4. Click in code module window.
5. Paste macro code.
6. Exit to excel.

How to run macro

1. Go to "Developer" tab on the ribbon
2. Click "Macros" button
3. Select "ListWorkbooks" macro
4. Click "Run" button

Download example file

List all open workbooks and corresponding sheets.xlsm

Related posts:

1. Excel vba: Save invoice data
2. List all tables and corresponding headers in a workbook (vba)
3. Excel udf: Sumif across multiple sheets
4. Basic data entry (vba)
5. Excel vba: Create a Print button

Cell B3 and C3 are input cells. When you click button "Add", the data in cell B3 and C3 are copied to the first empty row in the list.

The last step in the code removes the values in cell B3 and C3.

Create macro

1. Open vb editor, press Alt+F11
2. Insert a new module

Step 1 - Find last nonempty cell in column B

We can find the last nonempty cell in column B with this code:

We want the first empty cell so we add one to the code.

This line of code saves the value in the variable Lrow. In the example given, the code saves the value 8 in Lrow.

Step 2 - Copy values

Let´s use the value in Lrow and copy values from cell B3:C3 to the first empty row.

Worksheets("Sheet1").Range("B" & Lrow & ":C" & Lrow) = Worksheets("Sheet1").Range("B3:C3").Value

becomes

Worksheets("Sheet1").Range("B8:C8") = Worksheets("Sheet1").Range("B3:C3").Value

Step 3 - Remove values

Worksheets("Sheet1").Range("B3:C3").Value = ""

Step 4 - Optimize code

Worksheets("Sheet1") is repeated many times. Let me show you how to optimize the code.

Sub AddText()
Dim Lrow As Single
With Worksheets("Sheet1")
   Lrow = .Range("B" & Rows.Count).End(xlUp).Row + 1
   .Range("B" & Lrow & ":C" & Lrow) = .Range("B3:C3").Value
   .Range("B3:C3").Value = ""
End With
End Sub

Step 5 - Create button

1. Go to developer tab
2. Click "Insert" button
3. Click "Button (Form Control)"
4. Create a button
5. Right click on your new button
6. Assign a macro
7. Select "AddText" macro

Final note:

There is a data entry form in excel but it is not on the ribbon.

Download excel file *.xlsm

Basic data entry.xlsm

Related posts:

1. Copy selected rows (checkboxes) (2/2)
2. Add values to a data validation list (vba)
3. Excel vba: Populate a combo box (form control)
4. Excel vba: Save invoice data
5. Quickly filter a column in an excel table

Sub ListTables()
Dim tbl As ListObject
Dim WS As Worksheet
Dim i, j As Single
Set WS = Sheets.Add
i = 1
For Each WS In Worksheets
    For Each tbl In WS.ListObjects
        Range("A1").Cells(i, 1).Value = tbl.Name
        For j = 1 To tbl.Range.Columns.Count
            Range("A1").Cells(i, j + 1).Value = tbl.Range.Cells(1, j)
        Next j
        i = i + 1
    Next tbl
Next WS
End Sub

Example

Sheet1, 2 and 3 contain three tables.

Run macro

1. Go to "Developer" tab
2. Click "Macros" button
3. Click "ListTables"
4. Click "Run"

Create macro

Open VB Editor, shortcut keys: Alt + F11.

Download excel 2007 macroenabled *.xlsm file

List all tables and headers in a workbook.xlsm

Related posts:

1. List all open workbooks and corresponding sheets (vba)
2. Excel vba: Select cell A1 on all sheets before you close a workbook
3. Excel:Print headers on every page
4. Change pivot table data source using a drop down list
5. Excel 2007: Collect unique values from several columns

In my workbook I have three worksheets; "Customer", "Vendor" and "Payment".

In the Customer sheet I have a table, tblCustomer, where I add new customers. Similarly, in the Vendor sheet I have a table, tblVendor, where I add new vendors.

In the Payment sheet I have a table, tblPayment, where i have three columns; Date, Amount and Name.

Now, here is what I want to do; In the Name column of the tblPayment, I want to create a drop down list in each cell, which would contain all the names from tblCustomer[Name] and tblVendor[Name].

This way I can fill in the Date, Amount and then select one of all the names available in the drop down list of my Name cell. Is this possible without using VB code or any macro? If so, please help me out with this.

Answer:

Yes it is possible without using vba.

Sheet: Customer

Sheet: Vendor

Sheet:Calc

Array formula in cell A2:

How to create an array formula

1. Copy (Ctrl + c) above array formula
2. Select cell A2
3. Paste (Ctrl + v)
4. Press and hold Ctrl + Shift
5. Press Enter once
6. Release all keys

How to copy array formula

1. Select cell A2
2. Copy (Ctrl + c)
3. Select cell range A3:A100
4. Paste (Ctrl + v)

Create named range

Named range formula:

Sheet: Payment

Create drop down list

1. Select cell C2
2. Go to "Data" tab
3. Click "Data validation"
   
4. Select List
5. Type =Names in Source field
6. Click OK

Download excel file *.xlsx

Basic invoice template.xlsx

Related posts:

1. Invoice template with dependent drop down lists in excel
2. Monthly calendar template in excel
3. Monthly calendar template #2 in excel
4. Identify duplicate invoice records in excel
5. Excel vba: Save invoice data

need to truncate the undermentioned column to create columns of weeks it has, for eg. 1/26/2011 was a wed, so need a column for 1/26/2011-sun 1/29/2011, and then onwards each week starting monday till 8/31/2011. please help asap

Assigned Dates
1/26/2011 - 8/31/2011
2/1/2011 - 3/30/2011
2/1/2011 - 3/30/2011
2/1/2011 - 3/30/2011
11/1/2010 - 2/11/2011
1/26/2011 - 8/31/2011

Answer:

Array Formula in cell  C2:

How to create an array formula

1. Select cell C2
2. Copy above array formula
3. Paste into formula bar
4. Press and hold Ctrl + Shift
5. Press Enter once
6. Release all keys

How to copy array formula

1. Select cell C2
2. Copy (Ctrl + c)
3. Select cell range C3:AH3
4. Paste (Ctrl + v)

Related posts:

1. Filter overlapping dates from date ranges in excel
2. Excel vba: Select value based on time and date
3. Create unique distinct year and months from a long date listing in excel
4. How to calculate missing months in a given date range in excel
5. Formula for matching a date within a date range in excel