.xlsm formulas not working

[SDixon]

Hi,

I have recently converted to Office 2007, and have changed an existing xls file to xlsm (mainly because I was having issues exporting out of Access 2007 to the xls file). My old issue has resolved, but a new one is coming up.

All of my data transfers to this xlsm spreadsheet as it should, but then all of my formulas that do various things with the data are not working.

They are either blank or have various errors. All of the data is as it should be and the formulas should work. The workbook is on automatic calculation, and I have even manually calculated the workbook and sheets, but still no joy. If I retype each formula in the EXACT same way it was, it will magically work.

Anyone have any ideas? I have searched high and low to see if anyone has this same issue and can't find anything on the net, since it's not an issue with manual calulation

Thanks,

Stacy

 

[Richard Schollar]

Hi Stacy

Are these formulas ones which include functions which were previously Analysis Toolpak Functions (eg NETWORKDAYS or WORKDAY for instance)?

Not really a solution, but possibly a way to deal with them more easily:

Try selecting a single cell and hitting Ctrl+H to open up the Find/Replace dialog. Open up the Options (if not already displayed) and change to Within Workbook. In Find What: type an = and same in Replace With (=). Click Replace All.

This simulates entering the formula again in your entire workbook.

 

[SDixon]

It's the weirdest thing, it won't even calculate the smallest formulas. I even have a formulas that are just =b2&c2 that won't calculate. The main formulas are basically =vlookup(a2, sheet1!b:d, 3, false)

It tried your trick and that seems to work, but this report and now another erpot is having the same issue. These used to be fully automated reports, do you have any idea what has happened or what I can do to permanently fix it?

 

[Richard Schollar]

Recently I saw someone have formula issues that resulted in #NAME? all over the place and that was down to an Internationalisation issue, but it sounds as if your formulas just don't recalc (unless you re-enter them) which seems a bit different. Are you based in an English-speaking country (eg UK, Australia, US) as I think it is less likely then that Internationalisation issues would be affecting you (unless there was some reason you needed eg to have your Excel or OS in French say)?

 

[SDixon]

I'm in the UK, so no lanuage issues as such, other than I'm US born and keep my language settings set to US on my computer. I like my Z's lol. You think this may an issue?

 

[Richard Schollar]

No, there are no function naming differences between English and American versions of Excel, so I don't think that has anything to do with it. When you recreated the workbook, did you just save down the existing .xls workbook as an .xlsm file or did you literally start with a blank xlsm and recreate all your formulas/data links etc?

 

[SDixon]

I just did the save as function. After reading your replies I'm starting to wonder if it is still part of my old issue of Access 2007 not recognising my named ranges in my workbook when exporting. It was creating all of the ranges as new tabs everytime I exported (no matter how many times i re-defined them).

Do you know if it is possible to create a macro in Excel to do the find and replace thing you suggested earlier?

 

[Richard Schollar]

Depending on the size and complexity of your workbook, I would consider completely recreating it in a fresh new file (workbooks can pick up corruption, so you might save yourself problems later by doing it now).

As for the Find/Replace yep you can record a macro - but the Within: Workbook setting can't be set via VBA (AFAIK anyway), so you would need to loop thru each sheet:

Sub Replace_Equals()
Dim ws As Worksheet
Application.Calculation = xlCalculationManual
 
For Each ws In Worksheets
   ws.UsedRange.Replace What:="=", Replacement:="=", LookAt:=xlPart
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub

 

[SDixon]

Thanks Richard, I appreciate all of your help. I'll try the macro first, and get that running until I can get some time together to do a complete re-build.

Thanks again