Microsoft Excel - Data Validation Source Problem

Question

I am trying to build a data validation that only displays items that match the criteria of another cell. I have a formula that returns multiple rows and that works okay when pasted in a cell.

This is the formula.

=OFFSET(Services!$B$1,MATCH(Packages!$A2,TableServices[Packages],0),0,COUNTIF(TableServices[Packages],Packages!$A2),1)

Now that I know that the formula works I want to add it to a cell's data validation, as a list, but when I enter the formula above I get an error

So I enter the formula as

=INDIRECT("OFFSET(SERVICES!$B$1,MATCH(Packages!A2,TableServices[Packages],0),0,COUNTIF(TableServices[Packages],Packages!A2),1)")

But then it displays the message

If I continue, there is no Data Validation on the cell.

When it is trying to validate the formula I can see that it throws the error having located the correct multiple rows as it highlights the selection and then stops.

I had a workaround where in the cell's data validation source I reference a cell that contains the non-indirect format of the formula and the validation works. But the problem with the formula outside of the Data validation source, if I then want additional lines and so copy down the validation, the validation is fixed to the first row.

i.e. I get

=INDIRECT("OFFSET(SERVICES!$B$1,MATCH(Packages!A2,TableServices[Packages],0),0,COUNTIF(TableServices[Packages],Packages!A2),1)")

Instead of

=INDIRECT("OFFSET(SERVICES!$B$1,MATCH(Packages!A2,TableServices[Packages],0),0,COUNTIF(TableServices[Packages],Packages!A2),1)")

=INDIRECT("OFFSET(SERVICES!$B$1,MATCH(Packages!A3,TableServices[Packages],0),0,COUNTIF(TableServices[Packages],Packages!A3),1)")

=INDIRECT("OFFSET(SERVICES!$B$1,MATCH(Packages!A4,TableServices[Packages],0),0,COUNTIF(TableServices[Packages],Packages!A4),1)")

=INDIRECT("OFFSET(SERVICES!$B$1,MATCH(Packages!A5,TableServices[Packages],0),0,COUNTIF(TableServices[Packages],Packages!A5),1)")

The problem the Data Validation seems to be that it is returning multiple rows to a single cell, however, as I want it displayed in a data validation list that should support multiple rows.

When I look at tutorials on the subject they don't seem to have any problems, and they often don't even have to use the INDIRECT Prefix. I have proved that there is nothing wrong with the formula.

Does anyone have any ideas why the Data Validation is having a problem?

Thanks

Martin

Answer 1

Change your table reference to range reference, then it will work.

In sheet services,

=OFFSET(B1,MATCH(E7,A2:A53,0),0,COUNTIF(A2:A53,E7),1)

Answer 2

The issue is caused by your formula result is an array instead of a range and the data validation list can only accept range.

You can put your formula result in another cell and use that range as drop down list.

For example, if you put the filter formula to a data validation list. It will get the error.

If you use =F1#, it will work.

Share your workbook without sensitive information will help to reproduce your issue and give you some suggestions.

You can upload to OneDrive, Google Drive, FileDropper or DropBox. Then post a link here.

Best Regards,

Snow Lu

Answer 3

Thanks Snow Lu for taking the time to reply.

I appreciate the problem is that I am returning an array, but I am finding many youtube tutorials that are doing the same thing (https://youtu.be/avVLznHODVA?t=375) and they don't have any problems and because my destination is a List I would have expected that to support an array.

I also confirm that having the formula outside the Data validation works, but when I copy the validation down to the next row it continues to reference the original line, so it would seem that I do need the formula in the Data Validation Source so the rows are incremented.

I will Upload the Excel spreadsheet as suggested.

Best regads

Martin

Answer 4

Below is a link to my test file

ValidationProblem.xlsx

Answer 5

Thank you Snow Lu, I can work with that although I am confused why if won't work as a Table.