Column Header Names in Functions

[aksarben]

I want to use a column header name from Row 1 in a function on multiple rows, and I want to use the exact same function in that column for each row.

Example: Consider a spreadsheet with a list of books, one book per row. Each row has columns for book title, price, and tax. The column for book price is named (cleverly) BookPrice. In the tax column, the function would look something like:

=BookPrice * 6%

Meaning I want the function to compute the tax for the book on that row.

I’ve done Google searches & looked on numerous Libre Office-related Web pages, and many seemed to imply this is possible, but none explained it clearly (or perhaps is out of date, or left out a step), because nothing I’ve done works. Every time try to create the function, I get either a name error or value error.

I know this is a little complicated. Let me know if I didn’t explain it clearly.

Here’s my Libre Office version:

26.2.3.2 (X86_64)
Build ID: 70e089b17412e4cb7773e41413306b17a2328c34
CPU threads: 12; OS: Windows 11 X86_64 (build 26200); UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

[EarnestAl]

1. Select the column with BookPrice
2. In the Name Box type BookPrice and press Enter
3. In the first calculation cell type =BookPrice*6% and double click the bottom right corner of the cell to copy the formula down

It is better to have the tax in a single place that can be changed when the tax percentage changes.
BookPriceTax135373.ods (20.2 KB)

[schiavinatto]

Aproveitando o arquivo do @EarnestAl, pode ser feito, também, desta maneira:

Vá em: Ferramentas / Opções / LibeOffice Calc / Calcular em Cálculos Gerais, marque [v] Localizar rótulos de colunas e linhas automaticamente.
Em D3 use a formula ='BookPrice'*$D$1

---

Using the file from @EarnestAl, it can also be done this way:

Go to: Tools / Options / LibeOffice Calc / Calculate in General Calculations, check [v] Automatically locate column and row labels.

In D3 use the formula ='BookPrice'*$D$1

[aksarben]

I think I see why was confused. After entering text in in the Name Box and pressing Enter, the text disappears. I thought that meant it didn’t accept the text. Someone needs to make UI clearer. Perhaps a tooltip that says something like “xxx added to name list.”

[aksarben]

OK, now I get different errors. The attached images are from my “actual” spreadsheet, which is supposed to compute when prescriptions should be refilled and when they will be exhausted. What does Err:522 mean? I assume the red squiggles mean the names are undefined? But I double checked the column headers and those are all valid column names.What is the spreadsheet trying to tell me?

[Wanderer]

@aksarben

What does Err:522 mean

It notifies you of a circular reference

https://help.libreoffice.org/latest/en-GB/text/scalc/05/02140000.html?DbPAR=CALC#bm_id3146797

[schiavinatto]

@aksarben

OK, now I get different errors.

Post an example of the file; it makes it much easier to locate the problem and suggest a solution.

[aksarben]

Prescription Tracking.ods (21.2 KB)

OK, here’s the file.

[schiavinatto]

@aksarben

OK, here’s the file.

[K3] =HOJE() + SE('Days Left';'Days Left';'Date Begun' + ('Doses Issued' / 'doses / day'))

And the named cells are eliminated.