Log In
Register
Home
Active Member
October 15, 2019
Offline
Hi
First time at this so apologies if I have got the formalities wrong.
My company received a file from an external party with a complex spreadsheet the problem is that there is a formula that is coming up with a #Value! error even though the formulae works.
I have tried F9 and Ctrl Alt F9 to fix but no luck
I have tried replacing = with = to fix but no luck
The only way is to go on the cell hit F2 and then enter. The problem with this is that there is thousands of cells and I am not sure if I reopen I will need to do it again.
The formula is also surrounded by {} before I hit F2 and enter then it is removed
Any ideas.
I have seen this before when changing the cell format but not to this extent.
Regards
Robert
See Answer
Trusted Members
December 20, 2019
Offline
I would have tried f9 to be fair.
Maybe something like this could work? (back up the worksheet first!)
Select the range of formulas then run the F2Each
Sub F2Each()
Dim c As Range
Dim r As Range
Set r = Selection
For Each c In r
c.Select
Application.SendKeys "{f2}"
Next c
End Sub
Active Member
October 15, 2019
Offline
Thanks for the suggestion but unfortunately that did not work
Could it be a version issue, our version is 2016
Also not sure if you had seen it but I added "The formula is also surrounded by {} before I hit F2 and enter then it is removed"
Active Member
October 15, 2019
Offline
It looks to be a version issue. I opened in Office 365 and it worked immediately
October 5, 2010
Offline
Hi Robert,
The {} means it's an array formula.
I'd say the forumla(e) is(are) referencing some cells that result in #VALUE whilst some of the cells give a valid result.
Without your workbook I can't say any more though - please attach it to your reply.
Regards
Phil
Trusted Members
December 20, 2019
Offline
What was the formulla?
Active Member
October 15, 2019
Offline
Hi
I have opened it in 365 and no issues. I then saved as xlsb and xls but when I open and end hit edit the problem is there again.
One of the formulae id =IF($B11=0,SUM(INDIRECT(CONCATENATE(ADDRESS(ROW()-$B10,COLUMN()-1,4),":",ADDRESS(ROW()-1,COLUMN()-1,4))))/INDIRECT(CONCATENATE("$B",(ROW()-1))),"") with curly brackets around it
When I edit the cell they disappear and the formula works.
In 365 are there any other options to change the compatibility or remove the array issue.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Did you check the blog page Phil linked to in previous post?
Another good post to read is this about the new dynamic array formulas.
I also suggest you to check this Microsoft guideline on array formulas.
Please attach a sample file with the formulas if you need more help.
Br,
Anders
July 16, 2010
Offline
Hi Robert,
The curly braces indicate that this formula was entered with the key strokes CTRL+SHIFT+ENTER to make it an array formula, as Phil explained. When you enter a formula with CTRL+SHIFT+ENTER Excel automatically puts the curly braces around it, this is a visual indicator to users that this formula has been array entered. When you enter array formulas they evaluate differently to regular formulas entered with just the ENTER key. Please read the post Phil linked to for a better understanding.
365 versions of Excel now treat ALL formulas as array formulas even though we only press ENTER (please see the post Anders linked to for Dynamic Arrays). It doesn't put the curly braces around the formula because, as I said, all formulas are now treated as array entered. You can still press CTRL+SHIFT+ENTER in 365 and it will put the curly braces around the formula for the purpose of backward compatibility, but the results will be the same in 365 either way you enter it.
When you save a file as .xls you are reverting the functionality of Excel back to Excel 2003, so your formula will not evaluate as an array formula without entering it with CTRL+SHIFT+ENTER. Hence why you get errors when you open the file after editing the formulas and only pressing ENTER to complete them.
Some formulas will evaluate in earlier versions of Excel with either CTRL+SHIFT+ENTER or just ENTER but you're likely to get different results. e.g. one result might be an error and the other result evaluates or you might be two results or two errors. It's easy to make a mistake.
There will be something wrong with the results being returned by the ADDRESS part of your formula. You can refer to this tutorial on troubleshooting formula errors, or you can share the file with us.
Mynda
Answers Post
1 Guest(s)
