How can we help you?

MyOffice Spreadsheet supports working with cells for which input date validation or selection of an acceptable value from a drop-down list is configured in third-party editors (cells with data validation).

The following limitations currently apply:

The data source for the drop-down list or the validation conditions for the date cannot be changed.

The prompt specified in a third-party editor cannot be changed.

Error messages created in third-party editors are not supported.

Drop-down lists inserted as a control or ActiveX are not supported.

You can drag one or more cells with data validation to autofill cells on the left, right, bottom, or top.

You can cut, copy and paste cells with data validation.

When you merge cells, the data validation condition is saved only from the upper-left cell of the range.

When you split cells:

The data validation condition is retained in the upper-left cell of the range if it was defined for that cell before the cells were merged.

The data validation condition is retained in each cell of the range if it has been defined for the merged cell. If the merged cell contained a drop-down list and a value was selected in it, it will only be retained in the upper-left cell of the range.

If a column or row that contains cells with data validation is selected on the sheet, the cells with data validation are copied to the new columns/rows when new columns or rows are pasted.

Information about working with drop-down lists

The data source for the drop-down list can be edited in the following ways:

Edit values

Add values within a range: When adding values to a range, a named range, a smart table, the values are added to the drop-down list according to their place in the range.

Delete values partially or completely: If one or several values are deleted, then the values are displayed in their place in the drop-down list (empty cell), if all values are deleted, then the drop-down list becomes empty.

The values in the drop-down list are always up-to-date. For example, if there is a function result in the source and the function's arguments change, the corresponding value in the list is updated.

The drop-down list displays the values as they appear in the source. In a cell, the display of the selected value is determined by the format of that cell. For example, if the drop‑down list contains dates, and the General or Text format is selected for the cell, then the selected date in the cell will be converted according to the format: the date 02.02.2020 will be displayed as the value 43863.

Enter a date

If a cell is configured to validate the specified date, in such a cell, you can enter:

Number. The application will automatically convert it to the appropriate date. For example, the number 1 will be converted to the date 12/31/1899.

The date in any supported data display format, including time indication. For example, 07/12/2023 10:30 AM.

Cell reference (for example, =B1). The date in a cell can be specified directly, using a function or a reference to another cell.

Function (for example, =TODAY()+6). The function can be of any kind.

An error message is displayed if:

You enter a date, a cell reference, or a function that does not comply with the validation.

You enter text into the cell or Text format is selected for the cell.

To enter a date in a cell, follow the steps below:

1.Select the cell. A prompt for entering a date will be displayed above the cell, if it was created in a third-party editor.

2.Enter the date in one of the ways described above.

3.Press Enter or fx_accept_icon button in the Formula bar. If the date is not entered as a reference or function, you can also click any other cell in the sheet or any column/row header.

If the date satisfies the validation conditions, you will quit the cell editing mode. The Date format is applied to a cell if another format was selected for the cell before entering the value (with an exception of the Text format).

If the date does not meet the validation conditions, then:

The date is highlighted.

The cell remains in editing mode.

An error message is displayed.

To close the error message and enter the correct date, do one of the following:

Start entering the date. The error message will close automatically.

Close the error message by clicking the data_checkin_close button at the top of the message or by pressing Esc. Enter the date.

Enter a value in a cell with a drop-down list

To enter a value in a cell with a drop-down list, follow these steps:

1.Select the cell. A prompt for entering a value will be displayed above the cell, if it was created in a third-party editor.

2.Do one of the following:

Expand the drop-down list and select the desired value. To work with the drop-down list, you can use the mouse or keyboard keys (see the table).

Enter the value manually. The entered value must match character by character with the required value in the drop-down list. The case of characters is not taken into account when entering a value. Press Enter or click the fx_accept_icon button in the Formula. bar, or click any other cell in the sheet/header of any column/row.

If the value entered manually is incorrect, the drop-down list is automatically expanded. An error message is displayed at the top of the list.

To edit the entered value, do one of the following:

Select the correct value from the drop-down list.

Start entering the correct value manually. The drop-down list will automatically close.

Close the drop-down list by clicking the data_checkin_close button at the top of it or by pressing Esc. Enter the correct value manually.

If the field is for manual entry only, the error message is displayed without the drop-down list.

The table lists the keys that are used when working with the drop-down list.

Action

Keyboard shortcut

Open the list

Alt+

Select a value

and

Apply value

Enter

Select the first value of the list

Home

Select the last value of the list

End

One screen down

Page Down

One screen up

Page Up

Close the list

Esc

The commands One screen down and One screen up are used to quickly view long drop-down lists. When executing commands, the list scrolls to the next or previous items.

Was this helpful?
Yes
No
Previous
Number formats
Next
Remove duplicates