I get error with "ROWS" in the formula - nested formula question
Hi,
I have an array formula I want to be a fast dynamic sorting and selecting function. Howeer I trie to nest it further with address, index etc. I get an error on the first "ROWS" in "1:"&ROWS(INS_KVAL); ... =IF(ISF(RAD(INDEX(INS_KVAL;LOWEST(IF(INS_KVAL<""; ROW(INDIRECT("1:"&ROWS(INS_KVAL);TRUE));"");ROW(IN DIRECT("1:"&ROWS(INS_KVAL);TRUE)));1)))=FALSE;ROW( INDEX(INS_KVAL;LOWEST(IF(INS_KVAL<"";ROW(INDIRECT ("1:"&ROWS(INS_KVAL);TRUE));"");ROW(INDIRECT("1:"& ROWS(INS_KVAL);TRUE)))));"") One working that make a txtreference to the array I want to show is: =ADRESS(RAD(INDEX(ShortNameList;MINSTA(OM(ShortNam eList<"";RAD(INDIREKT("1:"&RADER(ShortNameList);S ANT));"");RAD(INDIREKT("1:"&RADER(ShortNameList);S ANT)));1));4;1;SANT;"VARIABLES") However, I can't use it because of as soon as I trie to nest it further, I get ROWS error. Is there a limitation in Excel of some kind? What I eventually want: - Check data from one table - it's a one column named "INS_KVAL" - if the word "PAY" is there - If it is, it should look up in another column beside INS_KVAL and on the same row find a value -It is an array used formula and if there is just 3 "PAY" in the "INS_KVAL" list, just 3 values should occure in the array formula list. Rest should be empty. I thougt I just should add an IF(INS_KVAL="PAY"; ...) and an ADDRESS(ROW( .... ; kolumn;1;TRUE;Sheet1) , but .. Can anybody help me? I belive I have translated all formulas to english correctly for easy checking ... /Kind regards |
All times are GMT +1. The time now is 03:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com