LibreOffice 7.1.3.2 (x64) Calc, using python 3.8.
For the sake of this example I’ll be using the cell C3.
Get a cell’s absolute address. Outputs:
"$'Sheet 1'.$C$3"
cell = sheet.getCellByPosition(2, 2)
cell_absolute_address = cell.AbsoluteName
Remove the $ sign before the row reference
Use the method you prefer, I am using regex for removing the $ sign before the number 3:
pre = re.sub('\$\d*$', '', cell.AbsoluteName)
post = re.sub('^\$[A-Za-z0-9.\s\'\$]+[A-Z]', '', cell.AbsoluteName).replace('$', '')
relative_address = f'{pre}{post}'
(Probably not the best way to convert it to relative but that’s not the problem)
relative_address = '$"Sheet 1".$C3'
Edit: As erAck pointed out the name of the sheet should be within single quotation marks as following:
'$\'Sheet1\'.$C3'
But that was not the origin of the issue. You can check the solution to this problem down below
Insert it as a formula via setFormula() method inside
cell.Validation
validation = cell.Validation
validation.Type = "LIST"
c = f'INDIRECT(OFFSET({relative_address}; 0; -1))' # REFERENCES THE CELL TO THE LEFT
validation.setFormula1(c)
cell.Validation = validation
Now run the macro and check it’s verification formula and you’ll get this:
It says:
INDIRECT(OFFSET($'Sheet 1'.$C5; 0; -1))
Where it should be:
INDIRECT(OFFSET($'Sheet 1'.$C3; 0; -1))
Thank you in advance.