=INDEX('A2_Sensitivity Analysis'!$AJ$16:$BA$31,COLUMNS($A:I),ROWS($1:25))



This worked until I inserted a few rows on the worksheet 'A2_Sensitivity
Analysis'. Now I get #REF! error. I'm trying to reference cells in column
AV, in this case row 24 on 'A2_Sensitivity Analysis'.

The benefit of this formula is that I can drag it across, and it will
reference the other rows in the same column of worksheet A2_Sensitivity
Analysis.

I tried adjusting the formula, but since I don't really understand it I keep
getting #ref errors. Any suggestions on how to modify it? thanks very much.

Here is what happen

Index format is Index(Table Range, Row, Column). The function returns a
value from a table. the number of the Row or Column cannot exceed the size
of the table Your code returns 9 for the number of rows (columns A:I) and
25 for the number of rows. The size of the table is 16 rows ad 18 columns
(AJ to BA). You have the following

Index (Table 16 rows by 18 columns, Row 9, Column 25). 25 is larger than
18 so you get an error.

The problem was caused by the Table referencing sheet 'A2_Sensitivity
Analysis' and the columns not referencing a sheet. You code should of looked
like this

=INDEX('A2_Sensitivity Analysis'!$AJ$16:$BA$31,'A2_Sensitivity
Analysis'!COLUMNS($A:I),'A2_Sensitivity Analysis'!ROWS($1:25))

But this causes an error. When you added columns you changged the size of
the Table but the number of rows and columns did not change.


"SteveC" wrote:

=INDEX('A2_Sensitivity Analysis'!$AJ$16:$BA$31,COLUMNS($A:I),ROWS($1:25))



This worked until I inserted a few rows on the worksheet 'A2_Sensitivity
Analysis'. Now I get #REF! error. I'm trying to reference cells in column
AV, in this case row 24 on 'A2_Sensitivity Analysis'.

The benefit of this formula is that I can drag it across, and it will
reference the other rows in the same column of worksheet A2_Sensitivity
Analysis.

I tried adjusting the formula, but since I don't really understand it I keep
getting #ref errors. Any suggestions on how to modify it? thanks very much.