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

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

--
Don Guillett
SalesAid Software

"mlkpied" wrote in message
...
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

Hi
are your column headers your identifgier. If yes use an iNDEX/MATCH
combination. See:
http://www.contextures.com/xlFunctions03.html


--
Regards
Frank Kabel
Frankfurt, Germany

"mlkpied" schrieb im Newsbeitrag
...
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

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" wrote:

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

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

"Peo Sjoblom" wrote:

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" wrote:

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

Thanks Frank. I'm new to this. I thought I looked at the article but didn't
understand how to make a column for a related worksheet a variable that might
change in the index function when I inserted a column. I'm somewhat new to
this. Thanks

"Frank Kabel" wrote:

Hi
are your column headers your identifgier. If yes use an iNDEX/MATCH
combination. See:
http://www.contextures.com/xlFunctions03.html


--
Regards
Frank Kabel
Frankfurt, Germany

"mlkpied" schrieb im Newsbeitrag
...
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

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.

--
Don Guillett
SalesAid Software

"mlkpied" wrote in message
...
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

"Peo Sjoblom" wrote:

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" wrote:

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