Named Range error

[njs27]

I apologize in advance for the lengthy post.

Quick overview of issue:
Error in data validation: “A named range you specified cannot be found.” However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.

Full description:
I created a spreadsheet model (no VBA, just formulas, etc) in a workbook that another coworker took and added VBA to. However, he was not working with the most updated version. So last night I checked the sheets which had the same names, made sure there were no differences except the changes I had made since that version, and then moved the ones from my current workbook into his workbook.

On the sheet where the user was to input details of the report, there are 4 cells that each contain different lists (non-dependent). I want the user to only be able to choose one of the 4 drop down menus - that is, if any of the 4 cells are filled in, I would like the other 3 to default to N/A (or somehow not be allowed to be chosen). This was solved in a previous thread:
http://www.excelforum.com/excel-form...html?p=3382993
Using A1: =IF(COUNTA(A2:A4),NA(),G2:G6) from the other thread solution, I used a named range instead of G2:G6 and it worked well.

When I merged the files together, the 4 cells no longer worked at all. It was suggested that I look at the Name Manager, and some of te named ranges I had were in there multiple tiimes. I cleaned them up.

I tried to enter the same formula again, and it gave me the specific error: “A named range you specified cannot be found.” However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.

Any ideas?

I am unable to load the spreadsheet due to sensitive data, and removing the data would make the workbook make no sense.

Thanks, if you read this far.

[FDibbins]

I tried to enter the same formula again, and it gave me the specific error: “A named range you specified cannot be found.”

what is the formula?

However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.

You can see the name, but what is the range that it shows? Is it an actual range (and the correct 1) or an error?

[njs27]

what is the formula?


You can see the name, but what is the range that it shows? Is it an actual range (and the correct 1) or an error?

Formula is
=IF(COUNTA(A2:A4),NA(),ListOne)
It is the correct range in the Name Manager, with respect to both Value and Refers To columns.

However, now that I look at this more closely, it says the Scope is just the worksheet it is on. How can I change that?

[njs27]

Oh I think I found where I can do that. Let me try to see if that makes it work.

[Pete_UK]

The names you need might have been defined with only worksheet scope, rather than the full workbook, especially if there were other identical names there first. Better to remove all the named ranges that were duplicated and then to re-define them, and that should give them workbook scope.

Hope this helps.

Pete

EDIT: Ah, I seem to have confirmed what you have found yourself.

Pete

[njs27]

Yes, it worked! Thank you Thank you Thank you!!

[FDibbins]

Happy to help and thanks for the feedback