Variable values in Index function

[mlkpied]

I use the index function with INDEX(Personnel!A$1:$I$71,I78,9). I sometime
add rows to the Personnel tab so that it now has more than 71 rows or even
more problematic is when I insert a column in the array the column I am
looking for is now 10 instead of 9. Is there a way to change my index
function that will reflect these changes I make in the Personnel tab. Thanks
for any help. Michael

 

[Don Guillett]

Have a look at making a defined name range using OFFSET.

 

[Peo Sjoblom]

You can use a dynamic range

OFFSET(Personnell!$A$1,,,COUNTA(Personnell!$A:$A),COUNTA(Personnell!$1:$1))

Name it MyRange and use it as

=INDEX(MyRange,row,dolumn)

having said that, why don't you just use a larger range in your original
formula
like

INDEX(Personnel!A$1:$I$1000,I78,9)

so you can add without changing the formula


Regards,

Peo Sjoblom

 

[mlkpied]

I looked at the OFFSET function and think I understand it for the size of the
array. Thanks. My bigger concern is when I insert a column in the Personnel
tab which moves it from column 9 to 10. I'm not sure how to do that. Thanks.
Michael

 

[Don Guillett]

that's what this part does. Re-study offset.
COUNTA(Personnell!$1:$1))
Peo didn't show ,0,0 cuz it's not necessary when ,, will do.