[Libreoffice-bugs] [Bug 122378] conditional formatting with formula is VLOOKUP(C14, Region, 1, 0) is VLOOKUP(C14, #NAME?, 1, 0)

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=122378

Heinrich Padrutt <hpadrutt at ggaweb.ch> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Assignee|libreoffice-bugs at lists.free |hpadrutt at ggaweb.ch
                   |desktop.org                 |
                 CC|                            |hpadrutt at ggaweb.ch

--- Comment #2 from Heinrich Padrutt <hpadrutt at ggaweb.ch> ---
Created attachment 147900
  --> https://bugs.documentfoundation.org/attachment.cgi?id=147900&action=edit
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

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20181230/fdefbd8c/attachment.html>