List All Formulas and Addresses in Entire File

I have a file with 1M+ formulas and 30K named ranges. I am trying to aggregate and simplify both areas. In order to accomplish this, I ideally need to know either the number of times a named range is used through out the workbook and it's location (sheet/address) OR a list of all formulas in the workbook and then i just do a search off that list (albeit a large list). the issue that's coming up is that even with a macro like this http://www.vbaexpress.com/kb/getarticle.php?kb_id=409 it's taking entirely too long. now I concede that in order to do this analysis it may literally just take hours/days if a macro is sorting through that many cells, HOWEVER i was hoping there was a fast way. Is there a file contained in the folder when it's converted to a zip file that (whilst needs parsing) contains file meta to the tune of what formulas are being used and where. also a forum contributor even solved an issue related to this https://www.excelforum.com/excel-pro...ml#post5736452 but even this method once applied to the large workbook takes so long it locks up.

Goal (1 or 2):
1. count number of times a named range is used and where
2. list all formulas or get a file where this is located

thanks in advance!

thanks FireDept!

i made a tweak to place everything on one sheet. thanks!

Please Login or Register  to view this content.