Dec 29 2023 09:01 AM
Good afternoon,
I'm using =INDIRECT data validation to pull Name, Phone, and Email from a list of 42 Departments. Once I select the Department, the Name, Phone, and Email automatically populate as shown in the sample below. This is working perfectly for 39 Departments, however, three of the Departments give me a #SPILL! error. I've looked through all the spill error corrections and none of them apply. I have re-typed the entries, reformatted the lists, moved the lists around, and no matter what I do I still get the same error for those three Departments. Thanks for any thoughts you might have.
Dec 29 2023 09:20 AM
Since you seem to have anonymized the names in this file already, it is possible for you to post the actual spreadsheet so we can take a look at it? Post it on OneDrive or GoogleDrive with a link here that grants access to it.
Having said that, I've never seen INDIRECT used within a data validation dialogue as you're showing it, as a way to retrieve data based on a separate entry. That doesn't mean it's invalid--and obviously it is working for some situations, but that's why seeing the actual would be helpful.
Dec 29 2023 11:02 AM
Dec 29 2023 11:22 AM
@Bonnie107 Then please start by showing us what you have in the named range "Education_Lottery_Commission".
Dec 29 2023 11:35 AM
These are the three Departments that result in errors. They are set up, formatted, and named exactly as the other 39 Departments.
Education_Lottery_Commission | General_Assembly | Wildlife_Resources_Commission |
Elizabeth Buono | Chase Honeycutt | Mark Dutton |
919-301-3363 | 919-733-4292 | 919-707-0033 |
email address removed for privacy reasons | email address removed for privacy reasons | email address removed for privacy reasons |
Dec 29 2023 01:26 PM
email address removed for privacy reasons
But not the names and phone numbers? Or are those not the real ones? They certainly have no obvious marks to indicate they're anything but real.
Dec 29 2023 01:39 PM
So if the formatting, setup and naming is exactly the same, then we need to be looking for some other distinction. For example,
Those are just examples of things that could be distinctions that could provide clues.
Dec 30 2023 01:48 AM
Solution@Bonnie107 Based on the information you've shared thus far, the setup seems a bit questionable to me. I'm not sure why you've used =INDIRECT(C62) to create a data validation picklist for cell C63 when the same formula has already been input directly in cell C63. Moreover, the table you've shared suggests there is only one VFC contact per department. As such, the XLOOKUP function could probably be used instead of INDIRECT to eliminate the need for creating 40+ named ranges. Please see the attached workbook for an example of how this could be setup...
Note: I was unable to duplicate the #SPILL! error using the INDIRECT method in the attached workbook. If you wish to pursue a solution to this method, please provide a screenshot of the Name Manager window, including the Name, Value, Refers To, and Scope columns for the three defined names in question. Kind regards.
Dec 30 2023 01:48 AM
Solution@Bonnie107 Based on the information you've shared thus far, the setup seems a bit questionable to me. I'm not sure why you've used =INDIRECT(C62) to create a data validation picklist for cell C63 when the same formula has already been input directly in cell C63. Moreover, the table you've shared suggests there is only one VFC contact per department. As such, the XLOOKUP function could probably be used instead of INDIRECT to eliminate the need for creating 40+ named ranges. Please see the attached workbook for an example of how this could be setup...
Note: I was unable to duplicate the #SPILL! error using the INDIRECT method in the attached workbook. If you wish to pursue a solution to this method, please provide a screenshot of the Name Manager window, including the Name, Value, Refers To, and Scope columns for the three defined names in question. Kind regards.