Download it now!
Bug 122378 - conditional formatting with formula is VLOOKUP(C14,Region,1,0) is VLOOKUP(C14,#NAME?,1,0)
Summary: conditional formatting with formula is VLOOKUP(C14,Region,1,0) is VLOOKUP...
Status: RESOLVED DUPLICATE of bug 122420
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.1.3.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xls
Depends on:
Blocks: Conditional-Formatting Macro-VBA
  Show dependency treegraph
 
Reported: 2018-12-30 10:26 UTC by Heinrich Padrutt
Modified: 2019-08-09 11:17 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
complete sheet for different month and different payout (41.56 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-12-30 14:42 UTC, Heinrich Padrutt
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Heinrich Padrutt 2018-12-30 10:26:00 UTC
Hi
In my short Excel I use conditional formatting with formula. Using Excel it works, using LibreOffice Calc it doesn’t work.
The problem seems to be in the conditional formatting as the Name “Region” is not valid after the value in the name “Region” changes.
Conditional formatting Formula is  VLOOKUP(C14,Region,1,0)    
as soon as the value in the “Region” changes it get this error
Conditional formatting Formula is  VLOOKUP(C14,#NAME?,1,0)

This formula is used in Range D14:D44 with ConditionlaStyle_1 which also does not work

LibreOffice Version 6.1.3.2 (x64) same in Version 6.2

If it helps I can attached the full Excel

Regards, Heinrich
Comment 1 Oliver Brinzing 2018-12-30 13:14:44 UTC
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. 
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Comment 2 Heinrich Padrutt 2018-12-30 14:42:11 UTC
Created attachment 147900 [details]
complete sheet for different month and different payout

This sheet is used for additional payout.
Cell C14 used to change date for any month; Conditional formatting fail when change
Cell L6 use P57:P59 select region fails when changed; Conditional formatting fail when change

VLOOKUP(C14,Region,1,0) D14:D44 
after change VLOOKUP(C14,#NAME?,1,0) after change never turns on ConditionalStype_1
	

VLOOKUP(C14,Region,1,0) F14:F44 turns on ConditionalStype_2
after change VLOOKUP(C14,#NAME?,1,0) after change does not turn on ConditionalStype_2

Cell I6 change value does work use P65:P66
Formulas in sheet
B14:B44
=IF(ISNA(VLOOKUP(C14,Region,2,0)),"",VLOOKUP(C14,Region,2,0))

C14:C44 date and Cell +1

D14:F44
Conditional formatting

H14:H44
=IF(OR(WEEKDAY($C14,2)>5,D14>="""a"),$G$57,$H$57)

O14:O44
=SUM($J$13*J14)+($K$13*K14)+($L$13*L14)+($M$13*M14)+($N$13*N14)

Row 49:50  summery of each row

D58
=TEXT(C14," MMMM")

D61
=CONCATENATE(C58,D58)

G57
=IF($I$6="yes",G58,G59)

H57
=IF($I$6="Yes",H58,H59)


Sheet Input list different Regions with different input for December 18 to December 2019



VBA
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$C$14", "$L$6")) Is Nothing Then Exit Sub
    
Dim cell As Range
'Dim rng As Range
Dim RangeName As String
Dim CellName As String
Dim ChRange As String
    ChRange = Range("L6").Value
    
'Range of Cells Reference (Workbook Input)
If ChRange = "DS" Then
  RangeName = "Region"
  CellName = "B4:C23"
    Set cell = Worksheets("Input").Range(CellName)
    ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
 
 Else
    If ChRange = "SR" Then
    RangeName = "Region"
    CellName = "H4:I23"
      Set cell = Worksheets("Input").Range(CellName)
      ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
  Else
        If ChRange = "TI" Then
        RangeName = "Region"
        CellName = "M4:N23"
          Set cell = Worksheets("Input").Range(CellName)
          ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
    End If
  End If
End If
' Kopieren Makro
    Range("B14:B44").Select
    Selection.Copy
    Range("D14:D44").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     Range("G14").Select
     Application.CutCopyMode = False
     ActiveSheet.Name = Range("D61").Value
End Sub
Comment 3 Heinrich Padrutt 2018-12-31 06:55:24 UTC
Added my sheet and described my formulas.
Comment 4 Oliver Brinzing 2018-12-31 12:44:51 UTC
steps to reproduce:
- allow macro execution: Tools/Options/LibreOffice/Security/Macro Security
- allow VBA Macro execution: Tools/Options/Load/Save/VBA Properties 
- restart LO
- open spreadsheet
- select "SR" from cell L6
- macro "Worksheet_Change(Range)" starts

it seems adding the already existing named range "Region" with 
the new range causes lo to invalidate the conditional formula:
VLOOKUP(C14,#NAME?,1,0) 

i am not familiar with excel vba macro's but this seems to work for me:

'   ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
    ThisWorkbook.Names.Add Name:="tmp", RefersTo:=cell
    ThisWorkbook.Names("tmp").Name = RangeName

first add a new named range "tmp" pointing to the new range,
then rename it to "Region".

i tend to say this is not an lo issue.
Comment 5 Oliver Brinzing 2018-12-31 13:11:44 UTC
(In reply to Oliver Brinzing from comment #4)
> i am not familiar with excel vba macro's but this seems to work for me:
> 
> '   ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
>     ThisWorkbook.Names.Add Name:="tmp", RefersTo:=cell
>     ThisWorkbook.Names("tmp").Name = RangeName

please ignore this, seems not to work
Comment 6 Oliver Brinzing 2018-12-31 13:40:08 UTC
maybe changing the cell reference will help:

Rem  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
ThisWorkbook.Names("Region").RefersTo = "=Input!$B$4:$C$23"
Comment 7 Heinrich Padrutt 2019-01-01 14:08:57 UTC
Rem  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
ThisWorkbook.Names("Region").RefersTo = "=Input!$B$4:$C$23"

Does not really help using LibreOffice Calc but at least VLOOKUP(C14,#NAME?,1,0) ,#NAME? is now Region but still does not update.
Works perfect using Excel.

It seems the problem not updating the vlookup formula is also know in bug 122394 as you sent me. 
Please enplane in more detail this and how to use lo api using my example.
btw.:
the problem does not happen if named ranges content is changed via lo api,
see "test_vlookup_changed_range.ods"
Comment 8 Oliver Brinzing 2019-01-02 12:57:07 UTC
(In reply to Heinrich Padrutt from comment #7)
> Please enplane in more detail this and how to use lo api using my example.

you can for - example change - the macro code like this:

If ChRange = "DS" Then
 ThisComponent.NamedRanges.getByName("Region").Content = "$Input.$B$4:$C$23"
 Else
  If ChRange = "SR" Then
     ThisComponent.NamedRanges.getByName("Region").Content = "$Input.$H$4:$I$23"
  Else
     If ChRange = "TI" Then
       ThisComponent.NamedRanges.getByName("Region").Content = "$Input.$M$4:$N$23"
    End If
  End If
End If

but as mentioned in issue https://bugs.documentfoundation.org/show_bug.cgi?id=122420 it seems the vba "worksheet_change" listener is lost.
so you need to do the code change with excel.

if you want to work with LO IMHO the best solution would be to exchange the vba code with LO basic code an save with ods file format.
Comment 9 Xisco Faulí 2019-07-08 15:49:00 UTC
(In reply to Oliver Brinzing from comment #8)
> (In reply to Heinrich Padrutt from comment #7)
> > Please enplane in more detail this and how to use lo api using my example.
> 
> you can for - example change - the macro code like this:
> 
> If ChRange = "DS" Then
>  ThisComponent.NamedRanges.getByName("Region").Content = "$Input.$B$4:$C$23"
>  Else
>   If ChRange = "SR" Then
>      ThisComponent.NamedRanges.getByName("Region").Content =
> "$Input.$H$4:$I$23"
>   Else
>      If ChRange = "TI" Then
>        ThisComponent.NamedRanges.getByName("Region").Content =
> "$Input.$M$4:$N$23"
>     End If
>   End If
> End If
> 
> but as mentioned in issue
> https://bugs.documentfoundation.org/show_bug.cgi?id=122420 it seems the vba
> "worksheet_change" listener is lost.
> so you need to do the code change with excel.
> 
> if you want to work with LO IMHO the best solution would be to exchange the
> vba code with LO basic code an save with ods file format.

Hi Oliver,
Do you think this is a valid bug ?
Comment 10 Oliver Brinzing 2019-07-08 17:14:46 UTC
(In reply to Xisco Faulí from comment #9)
> Do you think this is a valid bug ?

i think if:

>  ThisWorkbook.Names("Region").RefersTo = "=Input!$B$4:$C$23"

will keep the formula valid (still pointing to "Region") we can not call it a bug, cause removing a named range will make references invalid.

the main problem seem to be the missing formula update as described in issue:
Bug 122420 - FILESAVE: VBA Worksheet_Change Listener lost on saving .xlsm in LO
Comment 11 Xisco Faulí 2019-08-09 11:17:52 UTC
Closing as dupe of bug 122420 then

*** This bug has been marked as a duplicate of bug 122420 ***