To stop the worksheet name from coming automatically while referring a cell or range of cells

[arunmukherjee]

How to stop the worksheet name from coming automatically while referring a cell or range of cells in same worksheet in the same workbook in excel 2016

for eg refer to cell B5 of the attached excel file....here instead of "=B3+B4" the formula is taking as "=Sheet1!B3+Sheet1!B4" automatically and I have to change it manually. If I am referring to other worksheets then its ok, but in the same worksheets it creates confusion.

It occurs mostly in cases of terms of the formula appearing after a term which is referred to in some other sheets.

For eg "B3+B4" will come in normal notations if the formula is written in sheet1 and both "B3" and "B4" cells referred here are in sheet1

but

"Sheet2!B3+Sheet1!B4" will be the weird version of the formula even if you are writing the formula in sheet1 with reference to cell "B3" in sheet2 (for which the notation is ok) and B4 in sheet1 (for which the notation is bit unusual)

I hope you understand.

So if anyone can advice how to stop this from occurring it will be helpful. Thanks....

[#2]

@arunmukherjee  Use a Named Range instead of just a cell reference. You'll find that under Inseert....Name. You can name a single cell or a whole table, and then refer to it simply by name in any formulas.

Here's a quick and dirty example. THere are two worksheets in the workbook. The first does a lookup and then a calculation based on references to the other sheet, but using the name of cell ("Tax") in one case and a range ("ProdTable") in the other.

[#3]

@arunmukherjee 

That's new for me. How do you insert your formula, just typing in cell edit mode or some other way?

[#4]

@Sergei Baklan

Just typing in cell edit mode....it occurs mostly in cases of terms of the formula appearing after a term which is referred to in some other sheets.

For eg "B3+B4" will come in normal notations if the formula is written in sheet1 and both "B3" and "B4" cells referred here are in sheet1

but

"Sheet2!B3+Sheet1!B4" will be the weird version of the formula even if you are writing the formula in sheet1 with reference to cell "B3" in sheet2 (for which the notation is ok) and B4 in sheet1 (for which the notation is bit unusual)

I hope you understand.

Please see if this can be solved.

[#5]

@arunmukherjee 

Excel includes the sheet name if you visit another sheet then return to the active sheet.

[#6]

@Patrick2788 this was not the case for excel 2003 or 2007 versions

[#7]

@arunmukherjee 

I don't have access to Excel 2003 but I can confirm this does happen in Excel 2007.

[#8]

@Patrick2788 

Almost sure that was in oldest versions. 

[#9]

@arunmukherjee I was able to solve this last year by changing a setting in Google Drive File Stream, which is installed on my computer.  Previously the option 'Show who is editing Office files, and share with other editors if I am editing' was checked.  After unchecking this, it seems like this fixed the issue.

Since then, the google application has changed and the option looks different.  But might be worth checking if it's a file-sync interaction across google drive / box / dropbox / onedrive / etc by disabling them and see if it fixes the issue.

I agree, it is super annoying that excel references the active sheet as if it were an external sheet.  It makes the data unsortable and prone to errors.