ACCT 218 Exam 3

how many arguments can a function contain

255

Number of characters displayed in the formula bar

32,767

how many IF functions can be nested within

64

how many characters can a formula contain

8,192

F5

GOTO

Logical IF

IF(AND), IF(OR) - Purpose to control the answer

Alt F8

Macro Menu

F8

Moves through lines of Macro code when debugging a Macro - when in Visual Basic, it will start the debugger (Ctr;l Shift F8 to stop)

Ctrl + F3

Name Manager

F3

Paste Name / Paste List / Start with = and use F3 to pick name to paste link

Conditional IF

SUMIF, COUNTIF, AVERAGEIF - used to find records matching some rules and some math - all columns must be same size - Requires input cell or string values (">5")

Number of functions in excel

around 460

APPLICATION.screenupdating = True/False

causes the screen to refresh(stutter) or not when using a macro

Relative code

code that uses the active cell based on when the macro is ran ActiveCell.Offset(0, -1).Columns("A:B").EntireColumn.Select ActiveCell.Offset(3, 0).Range("A1").Select ActiveCell.Range("A1:D1").Select

Fill Across Worksheet

command to the Quick Access Toolbar

=HLOOKUP( )

(lookup value, table array, row index num, range lookup) =(150, $B$6:$D$12, 2, TRUE) - True lookup searches range of values

=VLOOKUP( )

(lookup value, table range, column index num) =(150, $B$6:$D$12, 3, False) - False lookup gives exact value, if not then #N/A

What is the advantage of putting them in the Personal workbook?

- It loads automatically when Excel is loaded so macros in that file are always available on that computer - The macros in the Personal workbook are then available to be used in any open Excel workbook. - The Personal.xlsx file is loaded when Excel loads but it is a hidden file so unless you go to View, and choose to Unhide it, you will not be able to edit the macros it contains.

Where can macros be stored (there are 3 different places)

- Normal to save the macro with the workbook they are built in - Can be saved to a new workbook (perhaps start a new workbook called macros.xlsm) and save all your macros in it and carry that file with you to load when you want to use your macros - Store them in the Personal workbook

What are the five different ways macros can be run from within an Excel workbook sheet?

-View Ribbon tab, Macros, View Macros, Run -Use Alt F8 (Macro) and the run button (same as above but fewer steps) -Use Alt F8 (Macro) and the Step into button -Use the keyboard shortcut keys -Use a macro button (or attach the macro to any object) Use a macro button on the quick access toolbar

number of characters displayed in a cell

1,024

F4

Absolute($)

Choices for controlling how your computer works

Advanced Excel options: - "When calculating this workbook" - "General" Trust center: - "Trust Center Settings for External Content"

Sheet names in formulas

Apostrophe is used when there is a space within a sheet name - 'Sheet 1'!B12 Exclamation mark is used when different sheet name (3-D Reference) is used in a formula - Section256!B12:D36

Absolute code

Code which runs at a specific place, no matter what the active cell is. It will always go back to the same cell of a certain worksheet. Columns("A:B").Select Range("A1").Select

Ctrl + Shift + F3

Create names from selection

Date Function

DATEDDIF(older date, new date, "D,M,Y")

Alt F11

Visual Basic editor

Name Manager

must start with backslash, or underscore, or a letter - also listed in this order