1 / 65

Construction Administrators Work Smart with Excel Programming and Functions

Construction Administrators Work Smart with Excel Programming and Functions. OTEC 2014 Session 78 Robert Henry. Cell References. Clicking into the Formula Bar or the Active Cell. will cause color coded emphasis on the cells that are referenced by the cell’s formula. COPY.

vaughan-best
Download Presentation

Construction Administrators Work Smart with Excel Programming and Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. Cell References Clicking into the Formula Bar or the Active Cell will cause color coded emphasis on the cells that are referenced by the cell’s formula. COPY

  3. Cell References By default, a cell reference is relative. This is what caused the errors.

  4. Cell References Make the cell reference absoluteby 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 the same.

  5. Cell References

  6. Cell References

  7. Named Cell References Change the cell reference in the namebox from “C1” to “L”.

  8. Named Cell References

  9. Named Cell References Change the cell reference in the namebox from “C2” to “A”.

  10. Named Cell References

  11. Named Cell References Change the cell reference in the namebox from “C3” to “E”,

  12. Named Cell References

  13. Named Cell References Substitute these values into the formula and copy down.

  14. Named Cell References Substitute these values into the formula and copy down.

  15. Named Cell References Substitute these values into the formula and copy down.

  16. Named Cell References Substitute these values into the formula and copy down.

  17. Cell Range References

  18. Cell Range References

  19. “VLOOKUP” Function

  20. “VLOOKUP” Function

  21. “VLOOKUP” Function

  22. “VLOOKUP” Function

  23. “VLOOKUP” Function Searches first column of the table array for the lookup value. Defined here by A6. Counts over by the column index number. Defined here by a 5. Returns that value found in that column. Since A6 has a value of 1, -5.48% would be the result of the VLOOKUP function.

  24. “VLOOKUP” Function

  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.

  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”.

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

  31. “AutoFilter Feature” Function Remove the Auto-filter by clicking its icon again in the “Data” tab. The worksheet returns to its original configuration unchanged.

  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.

  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.

  34. “Dialogue Boxes/ Data Validation” Feature

  35. “Dialogue Boxes/ Data Validation” Feature Click on “Data Validation” in the “Data” tab.

  36. “Dialogue Boxes/ Data Validation” Feature

  37. “Dialogue Boxes/ Data Validation” Feature

  38. “Dialogue Boxes/ Data Validation” Feature

  39. “Dialogue Boxes/ Data Validation” Feature Uncheck “Ignore blank”.

  40. “Dialogue Boxes/ Data Validation” Feature

  41. “Dialogue Boxes/ Data Validation” Feature

  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.

  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. “Excel Macros and VBA” Feature Now the “Developer” tab will appear on the excel ribbon. Activate “Record Macro”.

  47. “Excel Macros and VBA” Feature Click “OK” to continue. The VBA editor will record your actions at this point.

  48. “Excel Macros and VBA” Feature This macro is being created to clear out the old data fro the report.

  49. “Excel Macros and VBA” Feature Click “Stop Recording” to disengage the VBA editor.

  50. “Excel Macros and VBA” Feature Click “Visual Basic” to look at the code created by the VBA editor based on the actions performed while recording the macro.

More Related