User Profile
tomsuters
Copper Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: CHALLENGE: find column formula for validation of mandatory columns in a table with user data
dear mtarler JosWoolley Detlef_Lewin just to let you know I am still looking into this and will get back to you in the coming days with an extensive analysis. Conclusion so far is that I still dhave not seen or found a solution. Regards tomsutersCHALLENGE: 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: https://stackoverflow.com/questions/19787285/is-it-possible-to-have-array-as-an-argument-to-indirect-so-indirect-returns 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!Re: error in ADRES() function using an array constant parameter
JosWoolley Hi Jos, in fact my Excel version allowed me (to your surprise?) to use the comma as my Windows decimal sign and ALSO as horizontal separator in array constants leading to the erroneous result you described. Option 1 and 2 solved this by keeping the horizontal separator ni Excel as is and change the decimal sign in either Windows or Excel. They both work fine but I found the consequence "inconvenient" as the number representation changes in Excel respectively all Windows applications (without having to change the applications themself). So the obvious third option is to try to use a different horizontal separator sign in Excel but to my knowledge Excel does not support such an option or says which other sign to use in case the comma clashes with the decimal sign. Hence my question if such an option or fall-back sign for array consatants exist. In the mean time I learnt that the backslash sign '\' forfills that purpose alhough this is nowhere documented or officially supported by Excel I believe. In fact I found that if you switch the Windows locale from comma to dot, Excel automatically replaces the backslash by a comma in the array contants of the formula and vv!! So for me this problem now seems to be solved with t5he backslash and thx again for your input. Below I try to summarize what Excel computes for the 4 different cases: I already want to attract your attention to my next problem with array constants in ADRES() in combination with INDIRECT() but I will start a new topic for that. Hope you can help out there too! TomRe: array formula gives wrong result when using generated cel ranges
mtarler when stepping through the evaluation steps of my formula #3 (see pictures below), I discovered that Excel adds curly brackets around the result of the ROW() function.. This could be caused by the CSE action for the formula and 1) the fact that ROW() also allows array arguments and 2) is used inside the ISEMPTY() array function? I checked that the use of other non-array functions than ROW() inside ISEMPTY() do not create curly brackets around their result and do not cause errors. Maybe you can check ithrough formula evaluation that curly brackets do not appear around the ROW() result in your formula #3? That would at least show a difference between our CSE environments and would explain why the use of array capable functions within in array capable functions in my Excel version does not work. Thx for your time and effort.
