Stopping cell reference updates

[Scott Danzig]

Simple question:

I have one table in one worksheet with 100 rows of data, 4 columns.
Worksheet 2 has a table with 2 columns with 1000 rows, all referencing
two of the columns in the first table. So essentially I have one
table that's a subset of the larger table. However, when I insert
rows in the first table, the second table's references change too,
which is undesired. If cell B3 in table 2 points to cell B3 in table
1, and I insert a row above row 3, then cell B3 in both tables become
B4.. I'd rather have B3 in the second table still point to B3, and B4
in the second table point to what was B3 in the first table.

Any way you know of to achieve the behavior I want? Thanks.

- Scott

 

[Dave Peterson]

If you refer to the range like:

=INDIRECT("b2")

you can insert/delete rows and columns and it'll still point to B2.