Excel dropdown options in cell - add another?

Smythe

An Excel sheet has a cell with a dropdown list of options which can be chosen from in order to make an entry in that cell.

I want to make an entry in the cell which is currently not an option on the dropdown list. How do I do this? I want this to also be an option from the dropdown so that it can be chosen again as required.

Thanks.

Esel

Right-click the cell (or maybe arrow)?

namenotavailablE

Click the 'Data' tab then the 'Data validation' icon. Choose 'List' from the 'Allow' field to view or amend the existing items in the dropdown list.

Smythe

namenotavailabl wrote: »

Click the 'Data' tab then the 'Data validation' icon. Choose 'List' from the 'Allow' field to view or amend the existing items in the dropdown list.

Thanks - I tried this though I get the message as shown in the attachment.

i.e. The Source currently evaluates to an error. Do you want to continue?

namenotavailablE

The source is the list of options you want to have in the drop-down list- is that a range of cells or a 'named range'?

Smythe

namenotavailabl wrote: »

The source is the list of options you want to have in the drop-down list- is that a range of cells or a 'named range'?

I have not produced the Excel sheet myself, but having checked, it does not appear to be a 'named range'.

namenotavailablE

There's possibly some error in a formula of the cells being used for the List source.

If you could post another screenshot of the List source (it's obscured by the error message in your first screenshot) and also of the specific cells to which the list refers (e.g. cells A1:A10 of Sheet1 or whatever) it might be easy to fix.

Smythe

namenotavailabl wrote: »

There's possibly some error in a formula of the cells being used for the List source.

If you could post another screenshot of the List source (it's obscured by the error message in your first screenshot) and also of the specific cells to which the list refers (e.g. cells A1:A10 of Sheet1 or whatever) it might be easy to fix.

I've attached the error message.

I've also attached the message I get when I attempt to type something into the cell.

namenotavailablE

Is there a worksheet (it may be hidden) called References in the workbook? If so, check what cells X3 to X7 on it are showing. Those cells are supposed to be the allowed values for the dropdown and I suspect that there's a problematic formula in one or more of them.

If the cells show something like #N/A, the formula in them needs adjusting. the nature of the adjustment depends on the nature of the error so if you know how to evaluate the formula you should identify the error and required fix. Alternatively, if there's nothing sensitive in the workbook or if it could be made safe for general viewing, consider uploading a copy here and I or another could see if there's a simple fix.

Esel

Would it work if the OP made their (correct) entry in Reference ..X8 and then edited the =Reference formula to ...X8 instead of X7?

namenotavailablE

Esel wrote: »

Would it work if the OP made their (correct) entry in Reference ..X8 and then edited the =Reference formula to ...X8 instead of X7?

No- the idea of using a dropdown linked to a range of cells is to allow the user a choice of possible values e.g. choose a month of the year. Referencing to a single cell would eliminate that choice.

Esel

namenotavailabl wrote: »

No- the idea of using a dropdown linked to a range of cells is to allow the user a choice of possible values e.g. choose a month of the year. Referencing to a single cell would eliminate that choice.

That is not what I mean.

Change the formula to $X$3:$X$8, after putting the required value in the relevant cell in the Reference Worksheet.

namenotavailablE

It's worth a try but if there are still problems in some or all of X3 to X7 I think the problem will remain.

Esel

namenotavailabl wrote: »

It's worth a try but if there are still problems in some or all of X3 to X7 I think the problem will remain.

I can't see how the contents of Worksheet Reference!X3:X7 have any involvement.

The requirement is to add an option in the drop-down list. Inserting this value into Reference!X8 and amending the original Worksheet drop-down list formula to X8 should achieve this.

Ignoring any issues/errors with the existing Data Validation list - if there is a possibility that addition entries will be needed in a DV list then the definition should use a Dynamic Named Range rather than an absolute reference to a range.

A Dynamic named range will extend automatically to include any new entries in the list, an absolute reference will always just refer to, for example, cells X3:X7

Smythe

namenotavailabl wrote: »

Click the 'Data' tab then the 'Data validation' icon. Choose 'List' from the 'Allow' field to view or amend the existing items in the dropdown list.

When I now go to the 'Data' tab then the 'Data validation' icon, then the 'Data validation' button, this last button is now inactive. Would this perhaps be because I have ticked the box to allow multiple users to the sheet (one other person)?

namenotavailabl wrote: »

Is there a worksheet (it may be hidden) called References in the workbook?

Yes, I found a References sheet.

namenotavailabl wrote: »

Alternatively, if there's nothing sensitive in the workbook or if it could be made safe for general viewing, consider uploading a copy here and I or another could see if there's a simple fix.

Unfortunately I wouldn't be able to, as I'm working on it but don't own it.

Esel

You have the References sheet.

Look at the cells in question X3:X7 and look to put the required new option in X8.

If X3:X7 have user entered values, just enter the new option into X8.

If the existing cells are formulas, you will have to know how to extend that formula to X8.

Then edit the original drop-down formula from ...X7 to ...X8.

If you get this far, the drop-down list should now include the new option.