#NAME? error

English
#1

I have an excel spreadsheet created by somebody else which I need to complete. There are drop down boxes in this spreadsheet. Some of them work ok but others just come up with #NAME instead of the options. I am assured these boxes work for other people (using Excel) but trying to open in Libre Office Calc they don’t work for me. I’ve tried all options I can think of. I recently updated Libre Office in the hope that this would solve the problem. After updating I reopened the spreadsheet and got the message below:

“Warning loading document filename.xlsx:
The data could not be loaded completely because the maximum number of columns per sheet was exceeded”

I’m not sure if that would be the cause but can anyone help?

Thanks

#2

Hello

you are talking about 2 different things (which may be related depending on how the dropdowns are filled)

  1. “Warning loading document filename.xlsx:”
    This is due to the fact that Excel supports more than 1024 columns (i.e. 16,384 columns), which is the maximum in LiibreOffice

  2. Error #NAME
    see Error Codes in LibreOffice Calc - LibreOffice Help, which states for Error 525:

An identifier could not be evaluated, for example, no valid reference, no valid domain name, no column/row label, no macro, incorrect decimal divider, add-in not found

Regarding 2. you need to provide an example of the cell content. showing the #NAME result (formula).

#3

Thanks I wasn’t sure if the two were related. With regard to the #NAME I can’t provide an example of the content as it is just showing me #NAME nothing else

#4

Click in the cell with the error and look at the input line at the top of your window

#5

What does show #NAME? - the cell or the drop down? If it is the drop down then check were the drop down items are coming from (list, range of cells,…). The incorrect reference must be visible. If a reference yields #NAME you can resolve the reference (its name). If it is the cell showing #NAME got to the cell and go to Edit Mode (Double Click the cell or F2)

#6

how do I check where the drop down items are coming from?

#7

You need to go into design mode (Tools -> Forms -> Option: Design Mode), Right click - Control Properties -> Tab: Data on the dropdown and check options Type of list content and List content

#8

Thanks Opaque - when I go into design mode then right click on the cell I don’t get an option for control properties

#9

I wrote “Right click … on the dropdown…” and this means the drop down control box and not to click the cell. But I start to get the gut feeling that you don’t have a real drop down form control by a validity list by using Data -> Validity (having no example file sometimes is a real pain)

#10

If I go to Data Validity I get a box up and the criteria source is listed as INDIRECT(SUBSTITUTE($xfc$9," “,”"))

#11

Aha - “trust your gut”. This causes the #NAME? and now it turns out your two problems being related, since there is no column XFC in Calc (the last one is AMJ in Calc)

#12

ah so there’s nothing I can do to fix it? or could I create my own data list and add it to the drop down control box? Sorry for the stupid questions - I’m not an IT person just trying to muddle my way through and the people who supply the spreadsheet are not IT people either :frowning: Thanks so much for your help with this.

#13

I do not see any solution from a LibreOffice’s perspective, since we cannot see what is in XFC. The only thing might be, that you change the validity option. This you could enter anything but I think this wasn’t the idea of the inventor of the sheet (from my perspective a very, very strange approach to hide such a thing in column 16383 (if I calculated correctly).

According could I create my own data list and add it: Yes, if you know the data to appear. And take care about your wording: This is not a drop down control box from a form perspective of Calc.

#14

Hello @fuglyjowls :slight_smile: About the challenge with the error #NAME ?. Have you tried double-checking that both the formula and the Calc configuration use the same language?

I’m asking this because one possible cause of this #NAME ? error is that you try to use a formula in a language that does not match the present language configuration of Calc. If so, to resolve this challenge, choose one of the following two options:

  • Option 1: Adapt your formula. So that
    it is in a language that match the
    present language use by Calc.
  • Option 2: Change the language of your Calc. So that it match the language use by the formula. Each time you change the language, you might need to restart your Calc.

Below is the same answer as above. But with details & examples if you’re interested in those.

To find which language your LibreOffice Calc is presently using, navigate to “Tools > Options” Then search for the various language configurations. With focus on the configuration(s) which affect the language use by the formulas.

For example:

  • If your LibreOffice Calc is presently configured to use the French language. Then you need to write the formula in French.
  • If your LibreOffice Calc is presently configured to use the English language. Then you need to write the formula in English.

Here is a formula example in French =MOYENNE(A1:A2) Documentation at Fonctions statistiques - Quatrième partie

Here is the same formula example in English =AVERAGE(A1:A2). Notice that the spelling is different. But the end result is the same. Documentation at Statistical Functions Part Four

Steps to reproduce this #NAME ? error:

  1. Using Calc, navigate to “Tools > Options”. Configure your Calc to use the French language
  2. Try to use this English formula =AVERAGE(A1:A2).
  3. Calc will display this #NAME ? error. Because presently Calc does not understand English. Only French.
  4. Now to resolve this challenge, replace the formula =AVERAGE(A1:A2) with =MOYENNE(A1:A2). Where “MOYENNE” means “AVERAGE” in French.
  5. The error is resolve. The formula works.
#15

@Francewhoa Add a reminder to your answer about the Use English function names option in the Formula options and the recommendations will be absolutely complete (however, I’m sure that some users will not be helped either)

#16

I believe the issue is that the command is not the same in the program you are using as the program the file was created in. For example: MINUS in one program would be RAWSUBSTRACT in calc. Find the equivelant command word and alter it and it should fix the issue.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.