"ivygirl" wrote...
....
I'm trying to develop a rating worksheet for an insurance agency. The
rating tables are broken down first by territory (group of counties)
and then the premium is shown depending upon the class (group of
specialties) and limits desired. So, for example, for Territory 1, I
have a rating table with classes 1 through 8 and different limit
selections across the top, complete with corresponding premiums, like
so:
....

If you arrange the separate tables for each territory in a regular manner,
you could eliminate the need to use volatile functions like INDIRECT. For
example, with only 8 classes in each table, you could have column headers in
rows 1, 11, 21, etc., with the left column/top row cell in each table
holding the territory name (if they're hidden, you don't need a 'Class'
header in those cells). Add a 2-column table of territories in the same
worksheet with territory names in the left column and formulas giving the
row index in which the territory is found, e.g., with the table in columns X
and Y beginning in row 1,

X1:
SomeTerritoryName

Y1:
=MATCH(X1,$A:$A,0)

Fill Y1 down to match the entries in column X. If this worksheet were named
TBLS, use formulas in other worksheets like

=VLOOKUP(ClassEntry,
INDEX(TBLS!$A:$A,VLOOKUP(TerritoryEntry,TBLS!$X:$Y ,2,0))
:INDEX(TBLS!$D:$D,VLOOKUP(TerritoryEntry,TBLS!$X:$ Y,2,0)+8),
MATCH(LimitEntry,TBLS!$B$1:$D$1,0),0)