Forum Discussion
CHALLENGE: find column formula for validation of mandatory columns in a table with user data
You are challenged to come up with a solution for the problem described here:
My first attempt was to try and use an array formula of the following form:
{=SOM(N(ISEMPTY(INDIRECT(<a list of cell references to the mandatory cells in a row>)))=0} I failed as it turned out that EXCEL does not allow the INDIRECT() function to be used in array formulas and you need INDIRECT() to create the list of references to the mandatory cells in a row of table 2 from the column names in table 1.
I found a similar task with the same problem here: excel - Is it possible to have array as an argument to INDIRECT(), so INDIRECT() returns array? - Stack Overflow
I look forward to your suggestions. Please bear in mind that the formula should not be changed if the content of table 1 is changed!
Can you clarify why you have marked the third row in Table 2 as TRUE? That row has 'user data' in col1, col2 and col4? Should not col3 contain 'user data'?
Also, I assume from your previous posts that any solution must be for pre-O365 versions of Excel?Regards
It's not clear why you would use INDIRECT().
Maybe I don't understand the 'challenge'.
=LET( t,table_2, r,INDEX(t,ROW()-ROW(table_2[#Headers]),0), a,FILTER(r,COUNTIFS(table_1[mand cols],table_2[#Headers])), b,FILTER(a,a<>0), c,COUNTA(b)=COUNTA(table_1[mand cols]), IF(COUNTA(b)>1,c,IF(ISERROR(b),FALSE,c)))Also, I'm not sure from where all the confusion/misunderstanding surrounding INDIRECT not processing an array derives. Even in legacy versions of Excel, that function is perfectly capable of processing an array: the example in the link you posted, for example, is solved by the simple insertion of the N function, i.e.
=SUM(N(INDIRECT(A1:A5)))If the entries in A1:A5 are text, not numeric, then replace N with T (obviously in that case applying a SUM function would make little sense; nevertheless, the point remains that an array of returns is coerced). Even for a range comprising mixed datatypes it is possible to coerce the array appropriately.
Regards
JosWoolley In a previous post, we found some odd behavior when using INDIRECT and ROW(). Personally I try to avoid INDIRECT as much as possible.
Detlef_Lewin I didn't go through your solution but I believe tomsuters needs a 'legacy' formula
tomsuters check the attached. you may need to use CSE (ctrl-shift-enter)
=SUM( IF(ISNUMBER(MATCH(INDEX(Table2[#Headers],COLUMN($B$1:INDEX($1:$1,COLUMNS(Table2)))),Table1,0)), --ISNUMBER(SEARCH("empty",INDEX(Table2,ROW()-ROW(Table2[#Headers]),COLUMN($B$1:INDEX($1:$1,COLUMNS(Table2)))))), 0))=0I wasn't sure what <empty> meant so I took it literal and searched for "empty" as a "pass" and any pass would result in a FALSE but you could use ISBLANK instead of the ISNUMBER(SEARCH(... )) if it is actually blank like this:
=SUM( IF(ISNUMBER(MATCH(INDEX(Table24[#Headers],COLUMN($B$1:INDEX($1:$1,COLUMNS(Table24)))),Table1,0)), --ISBLANK(INDEX(Table24,ROW()-ROW(Table24[#Headers]),COLUMN($B$1:INDEX($1:$1,COLUMNS(Table24))))), 0))=0I included both versions in the attached.
This all said, I have the new Excel with dynamic arrays and such so can't test if it will work in a legacy version of Excel.
Yes, I already replied to that previous post.
As to the current problem, the following might be even simpler:
=SUM(N(INDEX( Table2[@[col1]:[col4]], N(IF(1, MATCH(Table1, Table2[[#Headers],[col1]:[col4]], 0))) ) = "") ) = 0Regards
