Construction Administrators Work Smart with Excel Programming and Functions. OTEC 2014 Session 78 Robert Henry

(1)

Construction Administrators Work Smart with Excel

Programming and Functions

(2)

(3)

Cell References

(4)

Cell References

Make the cell reference absolute by preceding the rows 1, 2, and 3 with a dollar sign ($). Then, when you copy the formula

(=B7*12*C$1/(C$2*C$3)) down column C, the formula stays exactly

(5)

(6)

(7)

Named Cell References

(8)

(9)

Named Cell References

(10)

(11)

Named Cell References

(12)

(13)

Named Cell References

(14)

Named Cell References

(15)

Named Cell References

(16)

Named Cell References

(17)

(18)

(19)

(20)

(21)

(22)

(23)

(24)

(25)

“AutoFilter Feature” Function



For medium-to-large quantities of data, using

Microsoft Excel’s AutoFilter is a quick and simple

way to filter through that information and find

what you need.



Filtering doesn't change your data in any way. As

soon as you remove the filter, all your data

reappears, exactly the same as it was before.



For medium-to-large quantities of data, using

Microsoft Excel’s AutoFilter is a quick and simple

way to filter through that information and find

what you need.



Filtering doesn't change your data in any way. As

soon as you remove the filter, all your data

(26)

“AutoFilter Feature” Function

Suppose we need to find all correspondence relating to Tower 5.

(27)

“AutoFilter Feature” Function

Use the Auto‐filter to locate the data needed. It is located in the “Data” tab.

(28)

“AutoFilter Feature” Function

Activating the Auto‐filter causes the drop down epsilon arrows to appear.

(29)

“AutoFilter Feature” Function

Activate the drop down epsilon in the

“Correspondence Subject” column and type “Tower 5”. Activate the drop down epsilon in the

(30)

“AutoFilter Feature” Function

The Auto Filter displays the rows in the worksheet that contain the search criteria “Tower 5”.

(31)

(32)

“Dialogue Boxes” Feature



There are times when an application needs to

get the attention of its user. It might be the user

has entered in some data incorrectly or the

application needs some help in determining what

to do next. The most direct way is through the

use of a dialog box.



There are times when an application needs to

get the attention of its user. It might be the user

has entered in some data incorrectly or the

application needs some help in determining what

to do next. The most direct way is through the

(33)

“Dialogue Boxes” Feature



Several excel intrinsic operations use dialogue

boxes. The most commonly known are the built

in to windows and excel.



One example is the creation of data validation to

control the input into a cell.



Several excel intrinsic operations use dialogue

boxes. The most commonly known are the built

in to windows and excel.

(34)

(35)

Click on “Data Validation” in the “Data” tab.

(36)

(37)

(38)

(39)

Uncheck “Ignore blank”. Uncheck “Ignore blank”.

(40)

(41)

(42)

“Excel Macros and VBA” Feature



With Excel VBA you can automate tasks in

Excel by writing so called macros. In part of the

presentation, learn how to create a simple

macro which will be executed from the excel

menu. First, turn on the Developer tab.



With Excel VBA you can automate tasks in

Excel by writing so called macros. In part of the

presentation, learn how to create a simple

(43)

“Excel Macros and VBA” Feature

In the “File” tab click “Options”

(44)

“Excel Macros and VBA” Feature

Check the “Developer” Box.

(45)

“Excel Macros and VBA” Feature

Check the “Developer” Box.

(46)

(47)

(48)

(49)

(50)

(51)

(52)

(53)

“Excel Macros and VBA” Feature

(54)

(55)

(56)

(57)

(58)

(59)

(60)

(61)

(62)

(63)

(64)

Construction Administrators Work Smart with Excel

Programming and Functions

OTEC 2014 Session 78 Robert Henry

(65)