Hi Mynda & Philip,
I had created a worksheet (Worksheet 1) with named ranges. However at the time, I did not realize the importance of limiting the scope to that particular worksheet. I later duplicated the worksheet. In the new worksheet (Worksheet 2), the numbers are not refreshing as expected when I change the months or the business name from the dropdown. When I opened the Name Manager, I figured the scope of Worksheet 1 is "Workbook" whereas the scope of Worksheet 2 is "Worksheet 2". I somehow feel this is the root cause of the numbers not updating/refreshing as expected in Worksheet 2. However, I am not able to change the scope as it's greyed out. Is there any workaround to edit the "Scope" of Named Ranges? I am also going to write in to MS to look into this.
PS: Please try to give a solution without a macro.
Thanks,
Rajiv
Hi Mynda & Philip,
Edit: I figured out the numbers not updating/refreshing as per expectations in Worksheet 2 was another formula issue. However my questions on the inability to edit "Scope" in Name Manager still remains.
Thanks,
Rajiv
Hi Rajiv,
You cannot edit the scope once you create the name, sorry. You need to re-create the name from scratch with the scope you want.
Mynda
Hi Mynda,
Is there a possibility of something going wrong in the report/error-refreshing-connections-dashboard-report if we define scope as "Workbook" instead of defining the scope as "Worksheet"? What are the likely cons of doing so?
Thanks,
Rajiv
Hi Rajiv,
Scope of the workbook means when you reference that name you can do so from any sheet in the workbook without qualifying the sheet the reference is on. When the scope is to a specific sheet it means if you want to reference that name from another sheet you must prefix the name with the sheet reference. e.g.
=Sheet1!Name
The cons are the extra name required when referencing that name from another sheet and the possibility of accidentally referencing a workbook scoped name in error.
Mynda
