Hi,
What would be the best way to get the array changed when using a index command?
INDEX(array;row_num;column_num)
As arrays I have data on other sheets and I use MATCH-command to look up correct account (row) and month information (column). The problem is, that as different years are on different sheets I can't change the year as Excel doesn't seem to allow the use of cell-reference as array.
For example: I want to get the number from 2003 january from account 001. All these infos are selected from data validation boxes. If the year 2003 corresponds to a named are DATA1 and is written on cell A1, the input is something like:
INDEX(A1; MATCH(JAN);MATCH(001)) [ingore the input of match...).
This doesn't work, if I manually write DATA1 as array, it works...
Wouldn't be a problem if macros would be allowed, but those are "against company policy"...
Help Appreciated!!
Yours Truly,
Ville
Helsinki, Finland
What would be the best way to get the array changed when using a index command?
INDEX(array;row_num;column_num)
As arrays I have data on other sheets and I use MATCH-command to look up correct account (row) and month information (column). The problem is, that as different years are on different sheets I can't change the year as Excel doesn't seem to allow the use of cell-reference as array.
For example: I want to get the number from 2003 january from account 001. All these infos are selected from data validation boxes. If the year 2003 corresponds to a named are DATA1 and is written on cell A1, the input is something like:
INDEX(A1; MATCH(JAN);MATCH(001)) [ingore the input of match...).
This doesn't work, if I manually write DATA1 as array, it works...
Wouldn't be a problem if macros would be allowed, but those are "against company policy"...
Help Appreciated!!
Yours Truly,
Ville
Helsinki, Finland