Price for apples: 1.10, NAME given APPL; bananas: 2.22; BANA, kiwis: 3.33, KIWI
| A | B | C | |
|---|---|---|---|
| 1 | Apples | 5 | $5.50 |
| 2 | Kiwis | 2 | $6.66 |
| 3 | Apples | 1 | $1.10 |
| 4 | Bananas | 5 | $11.10 |
To get the result, I would like to calculate the result with the formula C1 = B1 * LEFT(A1,4). But it does not work like this. LEFT(A1,4) returns APPL but it is not recognized as NAME.
If I use C1 =IF(LEFT(A1,4)=“Appl”,APPL*A2,0) I get the correct result. In this formula 0 (zero) is a placeholder only.
How can I proceed successfully?
If I understood correctly, you have set up defined names for the cells containing the price. You could get away with =B1*INDIRECT(LEFT(A1;4)) and pull/copy-paste that down, but that looks rather cumbersome and ugly to maintain and also every formula containing the INDIRECT() function needs to be recalculated on any modification of the sheet.
I’d rather use a lookup table like
| E | F | |
|---|---|---|
| 1 | Apples | 1.1 |
| 2 | Bananas | 2.22 |
| 3 | Kiwis | 3.33 |
and formula =B1*VLOOKUP(A1;$E$1:$F$3;2;0) copied down.
On a separate sheet I create names with different values.
$sheet2.$A$1 is named APPL and contains the value 1.10
$sheet2.$A$2 is named BANA and contains the value 2.22
and so forth.
On my “original” sheet1, I want to “extract” the first four letters in column 1 and use them as NAME, which has not worked so far.
=B1*INDIRECT(LEFT(A1;4)) results in the message #REF!
’
=B1*VLOOKUP(A1;$E$1:$F$3;2;0) is working. But this solution leaves me less freedom to regroup / change / alter data.
Thank you for your support.
Maybe you defined the names as sheet-local names (scoped to the sheet they are defined on, Sheet2). Then you’d need =B1*INDIRECT("Sheet2."&LEFT(A1;4))
If that doesn’t work, upload your document (edit your question with … and pencil, then use the upload icon).
If you wanna use the indirect() function, you need to define the names as global, then there will be no error message:
That’s not true. Names with sheet-local scope can be used with INDIRECT() as I indicated, prefixing them with the sheet name and dot like any reference. Unless you are using a years old LibreOffice version.
OK, you’re right.
Can we agree, that if Flla want’s to use the indirect() function with the names, without prefixing them, then the global definition would be necessary?
Yes.
Apples 5 =B1*INDIRECT($sheet2.,LEFT(A1,4)) displays #NAME?
Apple, r1c2: 1.11 > Name given ‘Appl’Does this work at all?
You did not follow what I wrote:
=B1*INDIRECT("Sheet2."&LEFT(A1;4))
"Sheet2." with . dot and in " double quotes " followed by & ampersand concatenation operator.
Your suggestion
=B1*INDIRECT("Sheet2."&LEFT(A1;4))
does not work for me. I end up with #REF!
.
A partial solution for me works as follows:
1 - Sheet2 - Names for cells as described [appl, bana, etc.]
.
2 - In addition, these names are combined in a group. Name given:- ‘fruits’
.
3 - Caveat:- the range of the group [fruits] must be as LEAST as long as the list in sheet1 but can be longer. So if I have 20 entries [r1 to r20] sheet1 the range of ‘fruits’ must be at least from row 1 to row 20 [sheet2 !!!] which is quite weird.
.
4 - The working formula in sheet1 [r1c3] is now
=B1*INDIRECT(LEFT(A1,4),fruits)
The result is the calculated value 5.55.
.
If you have any ideas to fix the problem mentioned under 3, I appreciate your suggestion.
.
Thanks for your support.
Whatever the actual name of your Sheet2 may be of course.
FIla1,
please upload your ODF type sample file here.
After reading the request from “Zizi64” to make my file available on the internet, I started the whole thing from scratch. I fiddled around with different combinations and ended up with this solution.
``
=B1*INDIRECT(LEFT(A1,4),)
Even changing the name of $Sheet2 has no effect on the result of $Sheet1 which is good.
Can you please confirm if this solution also works for you, please?
Many thanks to all who have contributed to finding the right solution (or one of several?)!