[Solved] Range name not reachable

[kiymik]

I'm using the same spreadsheet as template, a lot of formulas and lookup tables are present. The lookup tables are named.
After installing new version of Ooo (I don't remember from which to which) the range names disappeared in menu Data/Define range and Data/Select range. I can see that they are already present in navigator. I want to modify its range but couldn't find a way to do it. Any way to do it ?
Thanks,
Sabri

[RPG]

Hello

I think you use named range and not databaseranges. When you want modify the named ranges use:
menu --> Insert --> Names --> Manage

Romke

[kiymik]

Thanks Romke,
I think that is mainly used to name the fields for database access. I don't use database, I have a table of rows and columns, by VLOOKUP or INDEX fetching the desired cell content. In menu-->insert-->names there is no 'manage' selection. I tried to create range name by menu-->insert-->names-->define and tried to use it in formula, it worked. I have to check if it works for all functions I use, but even if it works well this is quite a lot of work to change all in my template. My range names are already lost
Sabri

[RPG]

Hello

There is a little difference between Apache Office and libreOffice. But the difference is not so big. Be sure you store your file in native OpenOffice format.

It is not clear what is your problem now you have to me. In this link you can find tutorials. Look for the calc tutorials and look maybe special for chapter 13. That it is for OOo3.3 is not a problem.

Romke

[kiymik]

I'm always using the .ods format saving my documents, I don't think the problem is because of format change.
The range names and database range names are seen at navigator. The range names were created by menu-->data-->define range by an older (Ooo 3.x.x or may be 2.x.x) version. The new version didn't show them in menu-->data-->define range, so I redefined them by appending '2' to its name. They then appeared within database ranges instead of range names. Both range names and database ranges can be used in formulas. My problem is that I'm not able to modify the range of a range name, menu-->data-->define range shows only database ranges. The navigator and range name windows are attached.

Another problem I always face is that, when I open a file which contains VLOOKUP function with range name, the function results with #NA, as in picture FirstLoaded.jpg. If I open any other spreadsheet first then open the same file I get correct results as in SecondLoaded.jpg. F9 does not function. The formula in one of the the cell is simply :
=VLOOKUP(U2;maclistesi2;8;0)
This problem is present for years, time to time I tried to mention it, no solution.

Sabri

[RPG]

Hello

Placing of 3 images does not help a lot. It is better to place the spreadsheet.

I'm always using the .ods format saving my documents, I don't think the problem is because of format change.

It is good you tell that. So we can be sure that is not the problem.

The range names were created by menu-->data-->define range by an older (Ooo 3.x.x or may be 2.x.x) version. The new version didn't show them in menu-->data-->define range, so I redefined them by appending '2' to its name. They then appeared within database ranges instead of range names

In the way you create the range-names it are databaseranges and not named ranges.

Databaseranges
I think it is better to use database ranges then named ranges. When you want use it in formulas then you can use Names. But I think there is so much risk on an error that I do not prefer to use them. Also I will point you to the tutorials there it is not possible for me to describes all rules.

Parts of the table
When you need parts of the table or need calculation then I prefer to use builtin tools like: filters, pivot table and the dbase functions. It is not so easy to understand them but I think you get faster results.

Another problem I always face is that, when I open a file which contains VLOOKUP function with range name, the function results with #NA, as in picture FirstLoaded.jpg.

Pictures give no information. The problem can be in the order of loading data. It seems to me from your description that the data is not available on first loading. I have no idea.

Database
I think it can be a good idea when you study what you can do with a database document. When you have made a database document then you can easy load the data you need.

In this link Villeroy give a nice example how you can use some built-in tools.

Romke

[kiymik]

Thanks Romke,
As I understand the range name I created with older version was already a database range but after changing version it changed to simple range name. Still usable in formulas but not editable.
I'm sending the simplified document and step by step procedure what I did. You are using Linux, most probably there is no such problem. My operating system is W7.
1 - Look at the task manager, assure there is no soffice.bin file loaded, if loaded then stop it.
2 - Load the file in http://www.vmtd.org/VLOOKUP-RangeName-ProblemSample.ods . All the formulas related to range names appears as #NA. File was too big to attach here.
3 - Modify any cell and try to save. I got the message "Error saving ....". There is no way to save it even with Save as.
4 - Terminate the program without saving.
5 - Open a clean spreadsheet, no need to modify anything and no need to save it.
6 - Load the attached file. All formulas are correct, able to save.
7 - Look at the range names and database range names in navigator. All exist. Data-->DefineRange shows only database ranges, range names are not reachable for modification.
The two problems I faced is present in this file.
Well, as you can see to solve my problem I created new database ranges and worked with them, but this is still a bug.
Regards,
Sabri

[RPG]

Hello

I think start again and make it simple.

I really think this is to complicated. It is sure so complicated that I do not understand it but other people can understand it maybe.

I think you are on the point now to learn what you can do with the more advanced tools of OOo-calc. I think start always
With simple tables.
Collect your data in this tables.
Process the data in the tables for the information you want have
Process this information so it can be good to show it.

Romke

[RPG]

Hello

You have also to learn simple debugging:
There is a different between the number 1 and the text 1. You use it in different way as it should be always clear what you want use it.

Romke

[RPG]

Hello

I think you are searching the problem on the wrong place. Search for all problem you have. Look for the simple formulas and then you go to the more complicated formulas. Even for formulas without an error it can give not the result you need for the formulas who call that cell. Test all the cell you have and need in formulas if they have a good value or even do have a value.

Maybe you have found all those problems then have no more or less problems.

Debugging
When I have to debug my own code I always make real simple formulas. You have to drill down the complete code.

Romke

[kiymik]

Hello,
You are correct that the problem I faced is because of complicated formulas. I tried the same thing with simple formulas and there is no problem.
Unfortunately what I'm doing here is really complicated, may be it would be better to use macros instead of formulas.
I will try to parse the formulas into several columns and combine them afterwards. This may work. If not I will try macros.
Thanks for your comments.
Sabri

[RoryOF]

Macros will be more complicated than formulae. Use the separate columns, and if necessary hide them.

[RPG]

Hello

I think start again and learn to use the more advanced tools. I think OOo can give you more advanced tools then you use now.

Make first simple tables in a spreadsheet. This simple table can be used later in a more complex way.

Collect all the data you get and you need.

Now you can only think to the data and to collecting of the data.

From time to time you want have information from the data. Some times this can be done simple. I think two tools for getting information of the data is: Pivot table and Consolidate. Sometime you have to insert one or more columns for getting the special results.

I really think you can do nice things with Database ranges. Do some calculating in the database range before or after sorting the database range. Then use the database range for the result in a Pivot table. Then at the end you can use the table wizard for make a nice looking result.

I new there are masters in use of spreadsheets who can do wonderful things with formulas in a spreadsheet. I cannot do it. It is possible some of those master will give you an answers.

Romke

[RPG]

Hello

I have looked a little more to your sheets. I have not the idea I do have a solution for your problem but maybe I can tell you a little about my own way of working maybe more how I learn about OOo.

I have always found that the data must be placed in tables. It was not always easy to do it. Dbase function, pivot table and filtering the tables are the most important tools. How more I understand those tools how better I can use them. Special the pivot table is real powerful. I think also the named ranges can be powerful maybe in combination with the pivot table.

What is the different between named tables and database ranges is not real clear. I can sum up some difference but I do not knew if I have the most important things. I think you can do calculations in named ranges and Database ranges but then it is maybe better to use a real database document even if it is based on a spreadsheet. For me the understanding of these powerful was in the beginning difficult to understand. But now it is good I have spent the time to learn them. Arrays are real powerful but they are to difficult for me to understand them so I avoid them.

Pivot table
What is so nice to the pivot table? When you have long table with dates then a pivot table can easy group this dates with data in a way it can help. There is no reason to use difficult formulas no the pivot table does it for you.


One of most important things I have learned: Let the computer work for you and work not for the computer.

Romke

[RPG]

Hello

=IF(L2="1";"Süper";IF(LEN(L2)>0 ;VALUE(L2)-1))

If I change on sheet 'Maç listesi formülleri' column M then I have a lot less errors. The formula must be changed in the complete column. I see real down in the column still some errors. I think there must be an error some where else. The error solved by me solved also a lot of other problems.

This is only searching for bugs. Most of the time the real problem can be on an other place then you think. Bugs searching is real important. I real often insert a column for checking information or split a complex formula in little parts.

For your information. I still have the idea that it can be important to make it more simple.

Romke

[kiymik]

Thanks Romke,
I'm aware that assuring calculations with numerical values avoids errors, in my case there is no problem with it. Anyway, I changed the formula of entire column.
My problem is not with errors. Some of the error messages in the sheet are intentional.
The problem I saw is only at VLOOKUP function. The 3 steps I did is shown in the picture.
1 - File is opened. Cell T2 says that there is error in formula.
2 - I deleted only the '=' sign and wrote it again.
3 - Formula is the same as it is loaded, but now correct result is present in the cell.
If I'm patient enough to do it for every cell (of one row and copy downwards) then I get correct list.
!!!!!!! After several assumptions and thoughts I solved what is going on.
I was taking care of range names contain only the standard ASCII characters.
But there is a big problem in Turkish with the letter 'I'. We have two types of it, capital and small with dot, capital and small without dot.
Standard capital I has no dot, whose small in Turkish is also without dot but it is not standard ASCII.
Standard small i has dot, whose capital also has dot in Turkish but it is not standard ASCII.
If my range name contains letter "i" then that range name is not recognized when it is first opened. It may be the because language support is valid after loading is completed.
Surprisingly if I use 'I' in range name it also doesn't work either.
After any spreadsheet is opened language support is now ready in dll's, so successive opened documents work without problem.
If seems any formula using that range name would result with similar error.
Now I will change all my range names to some word without 'i' and get rid of that problem.
This may appear for other languages in which similar conflicts are present.
Thank you all for all your efforts.
Sabri

[RPG]

Hello

I was taking care of range names contain only the standard ASCII characters.

I think use only Non standard ASCII characters in personal names and do not use the personal names not for lookups. I think it can be better to work with numbers in the lookup and then display the personal name. I think then you have no problems for this point. You see errors when you start the program the first time and later not. I do not see that difference. But even if I should that difference that can be a reason to start for debugging and make the program more simple. It is always a sign that there is a problem what I, as maker, do not understand. Make all thing more easy is for me most of the best solution.

Debugging
I will go back to debugging. I really think this is the real problem. You point to a single problem but I think there see only a little part of the problem. Follow how the formula is in that cell. Solve all possible and real problems you have there. It can be deeper then five formulas.

Indirect function
I think you use real often the function indirect. I'm not a real master of spreadsheets but I think it is better not to use them.

Ranges
You have learned to use more easy names. I think it is also better to make a choice between how you will point to ranges. I think make a real choice if you want use real cell names ,'Sheetname.A1' or want use database ranges or named ranges. Use a method what is working good for you. I think it is a choice and you will learn some method will better work with the function of your choice.

Functions
Some function do better with database ranges or not. Learn the function who work good with the other ideas you have to come to your wanted results. If you want use array functions then search on this forum for the name "ken Johnson"

Tables and sheets
I think it can be good to have no more then one table on one sheet. When you start with spreadsheet most of the time you start with simple things and it grows to more complex things. On one moment there is reason to make it more simple. Most of the time then there is also a reason to learn the more complicated function and or tools. I think you are on that point and be sure macros will not help you.

I do always trust the makers of the tool I use. I n this case I do mean the tool OpenOffice but it can be an other like a CAD program. What is important for me is to discover how the tool expect I do work with it. You can find lots of better answers then I can give. I try only that you start reading of those masters.

Romke