Excel 2010

Question 1

Refer to cell A1 on Sheet 2

Answer 1

=Sheet2!A1

p57

Question 2

Refer to cell A1 on Sheet 1 in Budget.xlsx

Answer 2

=[Budget.xlsx]Sheet1!A1

p57

Question 3

Refer to cell A1 on Sheet 1 in Budget for 2018.xlsx

Answer 3

=’[Budget for 2018.xlsx]Sheet1’!A1

p57

Question 4

Refer to a value in cell A1 on Sheet 1 in a closed workbook called OMSR Valuation.xlsx in X:\Valuations

Answer 4

=’X:\Valuations[OMSR Valuation.xlsx]Sheet1’!A1

p57

Question 5

Why is switching to R1C1 notation good for spotting errors?

Answer 5

In R1C1 notation, every copied formula is exactly the same. If one looks different from its surrounding formulas, there’as a good change that it might be incorrect.
p56

Question 6

Why is it useful to use named ranges in Excel?

Answer 6

Using names is especially important if you write VBA code that uses cell or range references. This is because VBA does not automatically update its references if youi move a cell or range that’s referred to in a VBA statement. For example, if a user inserts a new row, the VBA reference will now refer to the wrong value.
p59

Question 7

What are my options for naming a range?

Answer 7

Choose Formulas -> Defined Names -> Define Name

Formulas->Defined Names->Name Manager or ctrl+F3

Select the cell or range and then type a name in the Name box and press Enter

If the worksheet contains text that you’d like to use for names of adjacent cells or ranges, select the text and the cells to be named and choose Formulas->Defined Names-> Create from Selection.

p59

Question 8

Replace newly named cell references with the name in the formulas

Answer 8

Formula->Defined Names->Define Name->Apply Names

p60

Question 9

There’s a sales summary table with columns that group sales by quarter (Qtr_1, Qtr_2, etc) and rows that go by region (north, south, east, west). Use the intersection operator to locate the value of the South’s Q2 sales.

Answer 9

=Qtr_2 South
The intersection operator is a space
p61