I have an Excel sheet that fills up the cells in column B with a button. Some important notes are:
I use a Dutch Excel version that does not allow me to change language settings.
Because of that my formulas are also in Dutch and do not accept commas, but use semicolon instead. Spaces in formulas are also not allowed.
Column B is filled up with two sets of data, devided by a blank row (there can be data in other colomns in that row). The number of rows per data set varies every time the datasheet is used.
Row 1 and 2 contain headers
The user has to fill in "ja" of "nee" in colomn E by hand
If the data in column B contains the word "Test1" the formula in the adjecent cell in column A should give a consecutive letter of the alphabet.
If the data in column B contains the word "Test2" the formula in the adjecent cell in column A should give a consecutive Roman number.
Formula in A3:
ALS(EN(E3="ja";ISGETAL(VIND.SPEC("Test2";B3)));"1";ALS(ISGETAL(VIND.SPEC("Test1";B3));"A";""))
works fine, but when I change it to:
ROMEINS(ALS(EN(E3="ja";ISGETAL(VIND.SPEC("Test2";B3)));"1";ALS(ISGETAL(VIND.SPEC("Test1";B3));"A";"")))
it gives me an error.
Formula in A4:A100 (I do not expect more data to be filled):
ALS(E4\<\>"ja";"";ALS(ISGETAL(VIND.SPEC("Test2";B4));ALS(OF(A3="";NIET(ISGETAL(A3)));1;A3+1);ALS(ISGETAL(VIND.SPEC("Test1";B4));ALS(OF(A3="";ISGETAL(A3));"A";TEKEN(CODE(A3)+1));"")))
This formula works too, execpt when there is a blank cell in the cell above. Also not able to give me Roman numbers.
The porblems due to blank cells are my main concern. I think I can make a workaround for the Roman numbers, but is it possible without adding extra columns?
I added a screenshot of how the table looks like with my current formula (columns C and D are hidden). Instead of starting over with A or 1 after a blank cell it should continue.