Calc - apply a formula while referencing cell from another sheet?

English
#1

(Using LO Version: 6.4.7.2)

I know that I can reference a cell in another sheet using something like:

=$Sheet2.C3

What if I want to apply a formula to that reference, though? How do I do that?

I tried:

=$Sheet2.C3(LEFT(A3,(LEN(A3)-18)))

but it didn’t work. Is it possible to do this or must I break it into separate steps? What is the syntax, assuming it is possible?

edit:
Seems like this works:

=LEFT($Import_tree.A3,(LEN($Import_tree.A3)-18))

I guess you have to use the full sheet + cell reference each time the cell is mentioned.

thanks,
babag

#2

Or you can use Named cells. Just name a cell in one sheet, and you can reference it by its name on any other (or same) sheet.

1 Like
#3

For the record, this is incorrect syntax for anything AFAIK. A person has to start out with a function name, not a sheet or cell reference.

If the length of the full reference (Sheet2.C3) is the problem, take Zizi64’s advice below.

If you want to have a “soft” reference to another sheet, use INDIRECT. So, if the sheet name is in A1 then:

=INDIRECT($A$1 & "." & "C3") will reference cell C3 on the sheet named in A1.

#4

This is just to show how reference to another sheet works. You can please edit the formula to get exactly your requirement. I have not used C3 as I am not very clear what is the requirement.
=LEFT($Sheet2.A3,LEN($Sheet2.A3)-18)
The dot separator is used before each cell reference and the name of the sheet is placed before the dot. The default (no dot and no sheet name) will automatically use the current sheet.
Hope this helps.

#5

Thanks, bcronald.

So, it looks like the difference between what you’re posting and what I found that worked is that mine has an extra parentheses pair around (LEN($Import_tree.A3)-18). I’m guessing that doesn’t make much difference.

thanks again,
babag

1 Like
#6

Welcome babag.

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