Database range names in regular formulas? How to calculate a new value according to another column's name?

LobaLuna · February 8, 2023, 3:17am

Hi, everybody

I have a database range already defined (all columns are also named according to the first-row labels). I inserted a new column between two columns and I want to make a calculation using the column names, instead of the address.

I cannot make it

This database range is as follows:
Column ProdCateg, with values "“Accessories”, “Bikes”, “Clothing”
Column Subcateg, with values like “Fenders”, “Gloves”, “Tires”, etc.
Column Prod, with “Bike1”, “Bike2”, “Bike3”, and so on…
…
(more columns)
database-range-names-in-regular-formulas.ods (143.7 KB)

All my column names are properly registered in the Sheet > Names Manager dialog, so I can see them when entering a formula argument! I registered later, past the creation of database range, using Sheet>Named Ranges>Manage Names.

I added a new column to the right of column Prod, named ProdNum. The formula that I want to use is
=if(find("Bikes",ProdCateg,1)>0,"SI","NO")

But all I get is the text of the formula displayed. Yes, I checked that the setting View>Display formula is not enabled.

Why is this problem? Is it that database range names are only allowed in D-functions?

Maybe I have a misconception somewhere, but really I had read the Calc Guide and the online Help and cannot figure it out.

Wanderer · February 8, 2023, 8:11am

Replace with a simple =4+5 first. If thos shows 9 try the suggestion of @elmau
Otherwise I’d first try safe-mode or check, if the cell is formatted a text. Sometimes also “recalculate” from the menu helps…

Villeroy · February 8, 2023, 1:46pm

You mean:

=FIND("Bike";INDEX(Productos;ROW();2))

elmau · February 8, 2023, 5:12am

Dear @LobaLuna

I not see the range name definition in your sheet (for column A), but, if you set range name correctly, you can use next formula.

=IF(ISERROR(FIND("Bikes",ProdCateg,1)),"NO", "SI")

LobaLuna · February 10, 2023, 3:17am

Hi, everyone.

Thanks, @Villeroy, @Wanderer, and @elmau for kindly helping me.

In fact, @Wanderer’s hint about a bad formatting helped me find out why the value was not showing: Originally, since I was expecting a text value, I formatted the column as Text, but later changed to number; however, the ' (apostrophe) sticked to the cell (I do not know why… maybe it’s a bug?). After removing it, I was able to see the error values and later correct the formula.

I guess I accidentally erased the range names before uploading the sheet, @elmau. Thanks for correcting my formula, anyway!

Thanks for showing an alternative formula to achieve the same, @Villeroy.

For my own understanding, the database range names do not operate in any regular formula, as I verified. Only standard range names, created by hand or automatically.

Wanderer · February 10, 2023, 6:40am

No bug, but a common error on our (users) side: Formatting never changes the value inside the cell. But it is taken into account when we input data. Therefore “text” was sticky in your case and manual removing of a apostrophe triggers the detection again, but will not change, if formatting is set to text.
.
The trick behind “text in columns” as well as copy/paste to solve this is usually to trigger auto-detect again, after settings of format and locale were corrected.
.
Thanks for sharing your solution.