Formula breaks down when opening in microsoft office [closed]

asked 2020-04-27 21:37:21 +0200

avogadro gravatar image

updated 2021-06-01 07:06:11 +0200

Alex Kemp gravatar image

I have a recipe development calculator that I'd like to be able to open on machines that run excel. Everything opens up fine, except a specific formula breaks down.

=SUMPRODUCT($D$53:$D$56*VLOOKUP($B$53:$B$56,$'Data '.$A$2:$'Data '.$I$100,5,0))

Here is an example of the formula, which is used extensively through the document in different iterations.

the idea is that it takes an ingredient, finds the relevant data for that ingredient in a table (calories per gram) and then multiplies that by the weight.


flour 65g

find flour in the table, flour is 3.48 calories per gram, multiply 65 by 3.48, then repeat for the rest of the ingredients, and sum up the total. Then you get the total amount of calories for the total amount of things you're using.

When opened in excel, I get a #VALUE error. I've tried changing the syntax around a bit, to stuff like this

=SUMPRODUCT($D$53:$D$56, VLOOKUP($B$53:$B$56,$'Data '.$A$2:$'Data '.$I$100,5,FALSE))

but absolutely nothing changes. I've tried doing just a regular single vlookup to make sure that it's actually able to find the data, and it does that no problem. The rest of the formula work fine. This happens whether I open it up as ODS, XLS, whatever.

Interestingly if I use an online converter from ods to xls, and open that - it works fine. Until I remove protected view and then it breaks. I'm not sure what this means, whether it's just the last protected state of the file and the original data value are preserved or something. I hope this makes some sense and I'm happy to try and clarify anything.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-07-28 20:46:49.435337


Das your sheet "Data " really contain a space character rightmost of its name?

Opaque gravatar imageOpaque ( 2020-04-28 12:12:34 +0200 )edit

@Opaque yes it does, i didn't actually realise that though haha, i've just corrected it. But yeah, that doesn't seem to be the issue.

avogadro gravatar imageavogadro ( 2020-04-28 12:48:02 +0200 )edit