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
Search JabSto ::

Custom Search