Sheet# in Refrence changed to #REF
Posted on 2011-09-19
I have large Excel sheets which have 1000s refrences to differet cells in one another. We upgraded from Office 2007 to 2010 & now all the sheet#s in celkl refrences are changed to #REF.
e.g: Cell G2 had a reference before ='File Path\[2011-4XX.xlsx]457'!J9. where 2011-4XX.xlsx is workbook & sheet is 457 & Cell is J9. NOW
Cell G2 have this reference ='File Path\[2011-4XX.xlsx]#REF'!J9. where 457 is changed to #REF & due to this all of cells have invalid reference.
I have made column A with all the sheet Nos. Let say G2 was referenced to sheet 457. Now i have A2 with value of 457 (manual input).
what i need a kind of VBA code which picks the value from adjecent cell from column A & change all #REF in formula to that value. So everything comes back to normal.
Hope this explains well.
I appreciate your support