Forum Discussion
Formular changed in MS 365 and lead to error
Hi, I have been using this formula for years now and with Microsoft 365 I get an error message and the formula did not work as requested. This is the formula. The goal is to get a value ...
The XVERWEIS formula works. If A8 is in the other sheet I get the value of column S back. But if A8 is not in the sheet I get a 0. I would like to see either a value or a blank field instead of 0.
Is there a way to adapt the formula?
Best Regards
Britta
If the value of A8 is not present in column A of the other sheet, the formula will return a blank.
If the value of A8 is present in column A of the other sheet, and if the corresponding cell in column S is empty, the formula will return 0.
There are several ways to hide the 0:
- Apply a custom number format to the cell(s) with the formula such as Allgemein;Allgemein;
- Set Excel to hide zero values - see Anzeigen oder Ausblenden von Nullwerten
- Change the formula to
=WENN(XVERWEIS(A8;'offene Re. Sekr. 05.05.2023'!A:A;'offene Re. Sekr. 05.05.2023'!S:S;"")="";"";XVERWEIS(A8;'offene Re. Sekr. 05.05.2023'!A:A;'offene Re. Sekr. 05.05.2023'!S:S;""))