Jul 15 2019 05:32 AM
Jul 15 2019 05:32 AM
Hi everyone,
I am working on an excel file with multiple tabs and tables. There is no more than one table per tab. Some table are data I have copy/paste, some others are tables that I have created and contain formulas linked to my data tables.
So the thing is, I wanted to update my data and copy/paste my new numbers in a table named "BDD_UnitSoldSTD" on a tab named "BDD Unit Sold STD" however I was not able to find this tab.
I though someone deleted it by mistake so I decide to create a new one named the same so my formulas would linked immediatly without me having to update all formulas. But when I enter "BDD_UnitSoldSTD" as my table name, Excel is telling me that "this name already exist. Names must be unique" so the tab must be still exist somewhere...
I have looked everywhere, it is not on my hidden tabs, and the case "show all tabs" in the "options" menu is checked. I have also double checked all my table's name and no one have this name.
I don't know where it could be, do you know where I could look ?
Many thanks,
Marion
Jul 15 2019 07:32 AM
@Deleted
Marion, check Formulas->Name Manager, it shall show there is the table. Or in Address Bar (top left corner) enter the table name, you will be forward on it.
Jul 15 2019 07:41 AM
@Deleted
Hi,
Let's differentiate between Sheet Tab names & Tables (who have names as well.
If you are looking at a Specific Table and you do not find it, you can see a list of All Tables by clicking on the Formulas Tab >> Name Manager (Shortcut CTRL + F3)
If you are looking at sheet tabs then you can do one of the foollowing:
1- To the far left side of the sheet tabs >> Right click on the Navigation buttons >> You get a List of All the VISIBLE sheet Tabs >> double clicking on anyone of them >. will take you right there.
2- You can extract a list of ALL Sheet VISIBLE & HIDDEN in a new worksheet (and even create a 2 ways navigation), either by using Power Query or by Using VBA .
i explained that in 2 separate Tutorials , here are the links:
https://www.youtube.com/watch?v=jEr3uj1ljQ8
https://www.youtube.com/watch?v=V0KfbMZa_pY
You can also do it by using formulas and Functions which will only extract a List of the VISIBLE Sheets. Here is another Tutorial on how to do that:
https://www.youtube.com/watch?v=FeKgWSD8IWc
Hope that helps
Nabil Mourad
Jul 15 2019 07:54 AM
@Deleted
you have another option as well if my previous techniques don't get you an answer.
1- Add the INQUIRE Tab to the Ribbon:
File >> Options >> ADD Ins >> Manage (lower Left corner) >> Select COM Addins (from the Drop List) >> Go >> Check the Box for INQUIRE >> OK >> It will be added to the Ribbon
2- Let's use it in Analyzing the workbook:
Click on the INQUIRE Tab of the Ribbon >> Click "Workbook Analysis" to the very Left (File should be saved first) >> in the Window that opens >> Under Items Check "Workbook" & Check " Ranges" >. Then Click on Export To Excel >> It will create a report that tells you if what you are looking for Exists or Not & Where it is.
I have a Tutorial on using the Inquire Tab:
Here is the Link:
https://www.youtube.com/watch?v=yOHJJcXtQq4
Hope that helps
Nabil Mourad