Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
I have a question regarding countif formula. | Excel Worksheet Functions | |||
Problem with nested logical formula. | Excel Worksheet Functions | |||
nested formula with if condotion | Excel Worksheet Functions | |||
nested formula | Excel Worksheet Functions |