Microsoft Office Tutorials and References

In Depth Information

Define a dynamic range name

Suppose that we have only two regions in our list one month. We

don’t want the Camera tool to return two rows of good data and two

rows of #N/A. Instead, we want the tool to return only the rows of

good data. That is, the Camera tool must dynamically adjust to the

data available.

Top Regions

# e i n e

1 New York

Unfortunately, the Camera object doesn’t accept

formulas that are common in spreadsheets. In fact,

it doesn’t appear to accept any Excel spreadsheet

functions at all. But that’s no problem, because the

Camera object
does
allow us to use dynamic

range names.

193

2 Calif ornia

179

3 Kansas

165

4 Alabama

161

A

B

C

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

DataRows

5

MaxRows

4

UseRows

4

To create a dynamic Camera object, you define

a dynamic range name, then define the Camera

range in terms of that dynamic range name.

Top Regions

#

Region

Score

1 New York

193

2 California

179

3 Kansas

165

4 Alabama

161

The easiest way to begin is to enter the definition

into a cell, where it’s easier to create and test.

5 Montana

157

5

#N/A

#N/A

5

#N/A

#N/A

5

#N/A

#N/A

We’ll use the OFFSET function to return the

range that we need. In case you’re not familiar

with the function, here are its arguments:

5

#N/A

#N/A

5

#N/A

#N/A

=OFFSET(reference, rows, cols, height, width)

In any convenient cell, enter this formula:

=OFFSET($A$5,0,0,2+UseRows,3)

This formula returns a reference to the range A5:C10. When you

enter it, Excel returns a #VALUE! error message. That’s expected.

To test that the formula is working as expected, copy this formula

from your formula bar, press the F5 (Goto) function key, paste the