How can I convert multiple formulas from xls to xlsx?

[mtrav10]

I upgraded my computer and it came with Excel 2007. In my Proforma I have hundreds of formulas that are imported into this sheet from about 45 other excel spreadsheets. Do I have to manually save each one as an xlsx or (I hope) is there a shortcut. I have used other software like Google adwords editor for an example where I can select a word and change it to a different word and it will find every match and make the change. I hope this is possible.

Thanks in advance

[Richard Buttrey]

Hi,

What do you mean by Imported?

Do you mean copied and pasted, a link, a data connection or what?

An example would help and a more precise explanation of exactly what you're doing.

[mtrav10]

Sorry I am new to this. Is there a way that I can change (in the paths below) for example BOSTON.xls to BOSTON.xlsx without having to do each one manually? In this particular spreadsheet I have 484 lines times 12 rows or 5808 changes that i need to make.

Upgrade; ='C:\Users\Mark Travers\Desktop\REPORTING\Finances\[BOSTON.xls]2012'!$B$5

Upgrade; ='C:\Users\Mark Travers\Desktop\REPORTING\Finances\[BOSTON.xlsx]2012'!$B$5

I am assuming by the way that there is not an easier way than I am suggesting.

[protonLeah]

deleted....

[Richard Buttrey]

Hi,

Just select all the cells on the sheets where you have the formulae and do a block Find and Replace.
i.e. Find ".xls" and Replace with ".xlsx"

[mtrav10]

thanks for all your help so far. I tried to do a find and replace all but when i do that it opens up a another window and it looks like i have to go folder by folder to replace all of the formulas. each line has a slightly different formula

='C:\Users\Mark Travers\Desktop\REPORTING\Finances\[Washington.xls]2012'!$B$2
='C:\Users\Mark Travers\Desktop\REPORTING\Finances\[Washington.xls]2012'!$B$3
='C:\Users\Mark Travers\Desktop\REPORTING\Finances\[Washington.xls]2012'!$B$4
='C:\Users\Mark Travers\Desktop\REPORTING\Finances\[Washington.xls]2012'!$B$5
='C:\Users\Mark Travers\Desktop\REPORTING\Finances\[Chicago Finances.xls]2012'!$B$4

etc..

[Richard Buttrey]

Hi,

It doesn't matter that formulae are different - All you're changing are the .xls strings within the formulae.

Click the small square to the left of the column A letter to select all cells, then using the Find and Select icon put .xls in the Find box, and .xlsx in the Replace box.

[mtrav10]

Thanks again, another box opens up asking to update values is that what it should do? Then I have to update all 4800 of them?

[Richard Buttrey]

Are you saying that there are 4800 file names or 4800 formulas.

You will see as many 'Update Values' boxes as there are file names so if there were 4800 file names I can see that isn't practical.

[mtrav10]

Long story short, I have a "Master" file that pulls information from about 47 other files. In the Master each of the smaller files is represented with 8 columns down and 12 rows acorss so 96 seperate formulas. Now multiply that with 47 sections in that Master just like the first one. 47 files times 96 pieces or 4512 slightly different formulas.

Sorry to be such a pain but this is going to take forever if I have to do it manually.

Thanks

[Richard Buttrey]

If your sheet only contains 47 file names then you'll just get 47 Update boxes. Click the Cancel button each time a new box pops up and after 47 cancels all the .xls strings in all the 47 file names will have been changed.