Data validation and lists

[KatJ]

Hi all,

A question that may be complicated to read!

I have set up a spreadsheet to do the following:

1. Data is captured in columns A through H. These columns and 5 rows have
been set up as a list
2. Column A is a reference number. When a user populates column B, column
A automatically generates a reference number (sequential, 1 - n)
3. Column B is "Review Type" and there are only 3 acceptable entries.
Column B is validated against these 3 values, also set up in a list as
follows:
B35 - Peer Review
B36 - Expert Review
B37 - Formal Review

When I click in the data table to add a new entry, and select the dropdown
in column B I see the three entries above. However, if I select Formal
Review, the new line in my list populates with Expert Review. I can see that
as the new row is entered in my list, B35 - B37 slide down one row. Hence
I'm getting the new B37 value as opposed to the relative value.

I could change my sheet so that a free text field is entered first but I was
wondering if anyone knows another way to get around this issue.

Thanks

Kat

 

[Roger Govier]

Hi Kat

With a small list of 3 items, instead of pointing to a range holding the
values, in your Data Validation dialogue,
Select List>in the white pane for Source, type Peer Review,Expert
Review,Formal Review
Do not put an = sign in front of the List that you type (as you would if
you were pointing to an external source list)