Apache OpenOffice (AOO) Bugzilla – Issue 112940
VLOOKUP into another spreadsheet does not copy formatting
Last modified: 2024-03-26 20:06:28 UTC
Scenario: File1.ods is password protected. It does contain one Sheet called Sheet1 with 2 columns A and B with the value 1 in cell A2, value 2 in cell A3, and 3 in cell A4. We also have the value 11 in cell B2, the value 12 in cell B3 and the value 13 en cell B4. File2.ods is not password protected. It does contain one column A with the value 1 in cell A2, value 2 in cell A3, and 3 in cell A4. In the cell B2, we put the following formula: VLOOKUP(A2;'file:///PathOfMyFile/File1.ods'#$Sheet1.A2:B4;2;0) in order to get the values from the B column from the password protected File1.ods. -> We get #N/D as a reply of the Vlookup in the cell B2 of our File2.ods because File1.ods is password protected. Doing the same with two MS excel files, working with MS Excel, MS Excel will simply ask for the password and will get the external values. The same behevior occur even we first open File1.ods and type the password before opening File2.ods. I know a workaround could be a copy/paste with link (DDE) of the password protected file in a new sheet of File2.ods but it's not "day to day" workable for users.
Why put zero in sort order of function - data is sorted in ascending order? Without zero, correct value is returned. Bug is confirmed because font is Times New Roman 6.8 instead of Arial 10 in File2 column B containing vlookup result. Rev. 1499775 Win 7
You can VLOOKUP into password protected spreadsheets. And if you clicked "Cancel" on the password dialog, you'll get the "#REF!" error in affected cells, until you go Edit -> Links, select the spreadsheet and click "Update", when it will ask for the password again, and then the references will work. The problem in the original bug report was the 0 in VLOOKUP's last argument. If the only error is that the wrong formatting is used - "font is Times New Roman 6.8 instead of Arial 10 in File2" - then the bug summary should be updated to say so. But then again that seems to be the general way references work. If you put "value" into A1 and make it italic, and then put "=A1" into another cell, the "value" shown in that other cell won't be italic. Same for font, font size, etc. So is the bug relative to VLOOKUP or is it about references in general?