Strange Error in a drop down menu

Question

Hi Folks, i have a dropdown menu which has been working fine for months, it suddenly stopped working today and i cant find the error, would appreciate some assistance please.

Data Validation =Main!$IJ$5:$IJ$49971

dropdown is in A1

row error began on 32772

Row number listed

=IFERROR(MATCH(Dashboard!A1, Main!IJ:IJ, 0), "Selection not found")

Cant figure this out .. help !!

Answer 1

Hello Betulator,

I am an independent advisor, trying to help other users in the Community with my experience in Microsoft products. Please note that I am a user like you and don't work directly for Microsoft.

It seems like you're using a formula to populate a dropdown list in cell A1 based on data validation. The formula you provided is an INDEX/MATCH formula to find the selected value in your dropdown list.

Let's break down your formula:

=IFERROR(MATCH(Dashboard! A1, Main! IJ:IJ, 0), "Selection not found")

• MATCH(Dashboard! A1, Main! IJ:IJ, 0): This part is trying to find the value in cell A1 of your "Dashboard" sheet within the range Main! IJ:IJ. It returns the relative position of that value within the range if found, or an error if not found.
• IFERROR(..., "Selection not found"): This wraps the MATCH function to handle errors. If MATCH returns an error (indicating the value was not found), it displays "Selection not found" instead.

Given that the dropdown stopped working, let's troubleshoot:

1. Data Source: Ensure that the data source range Main! IJ:IJ is still valid and contains the values you expect for the dropdown. If the data source has been modified or corrupted, this could cause issues.
2. Reference Cell: Make sure the reference cell Dashboard! A1 contains a valid value that exists in the data validation range Main! IJ:IJ.
3. Cell Formatting: Check if there are any formatting changes or restrictions applied to cell A1 or the cells containing the dropdown formula.
4. Worksheet Protection: If your worksheet is protected, ensure that cell A1 and the cells containing the dropdown formula are not locked.
5. Check for Errors: Check if there are any other formulas or conditions affecting the dropdown behavior.
6. Data Range Limits: You mentioned the error started on row 32772. It's worth checking if there's anything specific about this row that might be causing the issue, such as data corruption or unexpected values.

By systematically checking these points, you should be able to identify the issue causing your dropdown to stop working. If you're still facing problems, provide more details about any error messages or unexpected behavior you encounter for further assistance.

Kind Regards, Fatai

Answer 2

Hi Fatai

Turns out i managed to correct the error, excel only allows 32200 ish rows in the dropdown, so i simply changed the dropdown from ij5:ij49000 to ij30000:ij50000, as i dont need the menu to track events that have finished this easy fix worked a treat, tx for trying to help.

Answer 3

some screen shots of the error

dashboard

main sheet showing the last row that works