Defining Scope for Names

Question

I'm trying to learn how to define scope for names however all of my searches return either VBA solutions (which isn't applicable) or how to define scope with the Windows version of Excel (which references menus that don't exist in the Mac Excel).

Does anyone have experience defining global and local scope on Excel for Mac, Office 365 Home? I can't figure out how to make them work. Using the Define Name menu or the Name Box seems to automatically reference the sheet in the range selector, but if I try to create another named range in another sheet it replaces the first range name. I thought there could be several local named ranges provided they always referenced the local sheet name.

Is it not possible to define scope on Mac Excel?

Thanks in advance.

Answer 1

Hi Captains,

I’d like to confirm whether you create the Name Box via Formulas>Define Name in multiple sheets in Excel 2016 for Mac and then the previous created name box is replaced with the latter one.

If yes, check if you are using the latest version of the Excel 2016 for Mac application. If no, update it and then restart the Mac to see and let us know the result. Meanwhile, upload a full screenshot of the page showing the version information of the Excel 2016 for Mac application you are using. You can access the page via Excel>About Excel.

If no, please list the detailed reproducing steps of the issue you are experiencing and upload some related screenshots.

Note: Remove any private information before uploading the screenshots.

Thanks,

Cliff

Answer 2

Hi Captains,

I’d like to confirm whether you create the Name Box via Formulas>Define Name in multiple sheets in Excel 2016 for Mac and then the previous created name box is replaced with the latter one.

If yes, check if you are using the latest version of the Excel 2016 for Mac application. If no, update it and then restart the Mac to see and let us know the result. Meanwhile, upload a full screenshot of the page showing the version information of the Excel 2016 for Mac application you are using. You can access the page via Excel>About Excel.

If no, please list the detailed reproducing steps of the issue you are experiencing and upload some related screenshots.

Note: Remove any private information before uploading the screenshots.

Thanks,

Cliff

Hi Cliff, thanks for the reply. I took screens of what I believe is the correct process. However part of the issue is that I could find no actual instruction on how to accomplish the declaration of scope for Names (variables) on Excel for Mac. Outlined is how I attempted to create 2 separate Names (jan_cost for Sheet1 and jan_cost for Sheet2), each local to it's own Sheet. However I'm keenly aware that this may not be possible on Mac Excel currently.

Excel version screenshot is at the end including screenshot showing I'm up to date.

#1 - Here in Sheet1 I named cell C2 jan_cost and as you can see, the range is referenced as (=Sheet1$C$2).<br><br><br>	#2 - Here in Sheet2 you can see cell C2 has no name declared.<br><br><br>
#3 - Still on Sheet2, I opened Define Name, I can see the other Name from before with it's same reference from Sheet1.<br><br><br>	#4 - Still on Sheet2, I reference (=Sheet2!$C$2) and name the range jan_cost.<br><br><br>
#5 - After hitting OK, you can see only one jan_cost exists and it now references Sheet2.<br><br><br>	#6 - Going back to Sheet1 and highlighting C2, you can see the Name has been reverted back to C2, as Sheet2!C2 now<br> has the name jan_cost.<br><br><br>

Answer 3

Hi Captains,

Thanks for your detailed updates.

If it is convenient for you to create the name box with different names in multiple sheets in Excel 2016 for Mac, I suggest you try it to see the result.

Thanks,

Cliff

Answer 4

Hi Captains,

Have you tried my suggestions? May I know whether you need further assistance on this issue?

Thanks,

Cliff

Answer 5

Hi Captains,

Have you tried my suggestions? May I know whether you need further assistance on this issue?

Thanks,

Cliff

Hi Cliff, yes using different names in different sheets works fine. I was simply hoping I could utilize the scope options. A few other Microsoft sources I had reached out to also replied that that functionality is not yet in Excel for Mac.

Thank you for all your help!