Ask Your Question

How to copy blanks from one sheet to another? [closed]

asked 2014-05-14 18:47:22 +0200

artfulrobot gravatar image

updated 2014-05-14 23:01:11 +0200

A cell in one workbook has a VLOOKUP formula that yields empty.

When I copy that cell and paste special into a second workbook (different file) I can't get the blank to come through - it's translated into a zero.

A zero and a blank mean completely different things in this dataset. I need a way to do this. For my purposes, a blank can be represented as a ZLS "", because it's going to get exported as CSV anyway.

Example sheet:

A1: =VLOOKUP("foo",B1:C2,2,0)
B1: foo
  1. A1 is blank (because the vlookup returns blank cell C1).
  2. Copy A1
  3. Paste-special somewhere else, without formula. You get a zero
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-22 17:47:15.339899


Please try: with =ISBLANK(VLOOKUP("foo",B1:C2,2,0)) in both files, to verify if the cell obtained with VLOOKUP is empty on both.

m.a.riosv gravatar imagem.a.riosv ( 2014-05-15 00:07:41 +0200 )edit

The ISBLANK(VLOOKUP()) returns TRUE, however ISBLANK(A1) (put this in another cell on the demo sheet) returns FALSE. TYPE(A1) return 8 which means formula. I think that this is a bug.

artfulrobot gravatar imageartfulrobot ( 2014-05-15 11:01:15 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-05-15 11:25:24 +0200

artfulrobot gravatar image

updated 2014-05-15 11:29:38 +0200

You can't. According to that post, the architecture of LO is such that a cell that is empty is deleted and therefore cannot contain a formula - so a formula that retuns blank (like VLOOKUP does - you can test by wrapping it in ISBLANK) cannot store this value in the cell. Also see the LibreOffice wiki page on handling of blanks.

The only way around this is to either return a ZLS with "", or to return NA() which uses the special #N/A non-value. These things copy and paste OK.

This is going to make my spreadsheet twice as slow: I have literally thousands of VLOOKUPs and I'm now going to duplicate them all like this:



edit flag offensive delete link more

answered 2014-05-14 21:55:08 +0200

m.a.riosv gravatar image

Are you sure, that the difference is not in the cell format, or the option to hide zeroes?.
Menu/Tools/Options/LIbreOffice calc/View - Display - Zero values.

Please can you show a sample formula with the issue.

edit flag offensive delete link more


I've edited the question, thanks for your interest.

artfulrobot gravatar imageartfulrobot ( 2014-05-14 23:01:37 +0200 )edit

Question Tools

1 follower


Asked: 2014-05-14 18:47:22 +0200

Seen: 370 times

Last updated: May 15 '14