<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body><span class="vcard"><a class="email" href="mailto:hpadrutt@ggaweb.ch" title="Heinrich Padrutt <hpadrutt@ggaweb.ch>"> <span class="fn">Heinrich Padrutt</span></a>
</span> changed
<a class="bz_bug_link
bz_status_NEEDINFO "
title="NEEDINFO - conditional formatting with formula is VLOOKUP(C14,Region,1,0) is VLOOKUP(C14,#NAME?,1,0)"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=122378">bug 122378</a>
<br>
<table border="1" cellspacing="0" cellpadding="8">
<tr>
<th>What</th>
<th>Removed</th>
<th>Added</th>
</tr>
<tr>
<td style="text-align:right;">Assignee</td>
<td>libreoffice-bugs@lists.freedesktop.org
</td>
<td>hpadrutt@ggaweb.ch
</td>
</tr>
<tr>
<td style="text-align:right;">CC</td>
<td>
</td>
<td>hpadrutt@ggaweb.ch
</td>
</tr></table>
<p>
<div>
<b><a class="bz_bug_link
bz_status_NEEDINFO "
title="NEEDINFO - conditional formatting with formula is VLOOKUP(C14,Region,1,0) is VLOOKUP(C14,#NAME?,1,0)"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=122378#c2">Comment # 2</a>
on <a class="bz_bug_link
bz_status_NEEDINFO "
title="NEEDINFO - conditional formatting with formula is VLOOKUP(C14,Region,1,0) is VLOOKUP(C14,#NAME?,1,0)"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=122378">bug 122378</a>
from <span class="vcard"><a class="email" href="mailto:hpadrutt@ggaweb.ch" title="Heinrich Padrutt <hpadrutt@ggaweb.ch>"> <span class="fn">Heinrich Padrutt</span></a>
</span></b>
<pre>Created <span class=""><a href="http://bugs.documentfoundation.org/attachment.cgi?id=147900" name="attach_147900" title="complete sheet for different month and different payout">attachment 147900</a> <a href="http://bugs.documentfoundation.org/attachment.cgi?id=147900&action=edit" title="complete sheet for different month and different payout">[details]</a></span>
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</pre>
</div>
</p>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>