Naming column in Index Function
I use the index function with INDEX(Personnel!A$1:$I$71,I78,9). When I insert
a column in the array the column I am looking for is now 10 instead of 9. The column name in the Personnel worksheet is PreFund. Can I change the index function column parameter so that if I insert a column it will still reference the correct column? Thanks. Michael |
Hi
see your other post. If you have a question to the ideas/solutions presented best to post a follow-up question :-) -- Regards Frank Kabel Frankfurt, Germany "mlkpied" schrieb im Newsbeitrag ... I use the index function with INDEX(Personnel!A$1:$I$71,I78,9). When I insert a column in the array the column I am looking for is now 10 instead of 9. The column name in the Personnel worksheet is PreFund. Can I change the index function column parameter so that if I insert a column it will still reference the correct column? Thanks. Michael |
One way:
=INDEX($I$1:I$71,I78) Another: =INDEX($A$1:$I$71, I78, COLUMN($I$1)) In article , "mlkpied" wrote: I use the index function with INDEX(Personnel!A$1:$I$71,I78,9). When I insert a column in the array the column I am looking for is now 10 instead of 9. The column name in the Personnel worksheet is PreFund. Can I change the index function column parameter so that if I insert a column it will still reference the correct column? Thanks. Michael |
JE McGimpsey wrote...
... Another: =INDEX($A$1:$I$71, I78, COLUMN($I$1)) ... Depends on the range starting in column A. More robust to use =INDEX($A$1:$I$71, I78, COLUMNS($A$1:$I$71)) to ensure referencing the rightmost column. --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! |
All times are GMT +1. The time now is 02:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com