SytelineMaster

When creating a new Syteline financial Statement, it is much easier to work on a copy of existing statement report, instead of starting from scratchy.  You can certainly use the Excel export/import to do the copying, but one part of the financial statement line, the total tab, would not get copied over that way. 

Syteline Financial Statements are stored in the following tables.

1) glrpth   statement definition

2) glrpthc   statement columns definition

3) glrptl      statement lines definition

4) glrptls    statement lines definition, total tab, line add up

5) glrptlc    statement lines definition, total tab, column defined.

By using Excel import, only the above 4) would not get copied over correctly.  You may use the following script to get the copy completed.

insert into glrptls
(rpt_id, seq, from_seq, to_seq, total_add)
select
‘New_Report_ID’, seq, from_seq, to_seq, total_add
from glrptls  tt1 where tt1.rpt_id = ‘Old_Report_ID’

One thing to remember, before running the above script, re-set sequence for both new and old report, make them both start from 10 and increment by 10.  This is to ensure both have the identical sequence.  Or, better yet, before you do the Excel import/export, re-set the sequence in old report. 

How to get a 2nd drop down box to update the range (Not just use C6:C7)based on selection in 1st drop down box.
Example:
If Fat, Use range from C6:C7
If Skinny, Use range from D6:D7

You can direct put the IF statement in 2nd drop down validation, like

=IF(Sheet1!$A$1="Fat", Sheet1!$C$6:$C$7, Sheet1!$D$6:$D$7)

Excel provides conditional formatting which allows you to change the color and other attributes of a cell based on the content of the cell. There is no way, unfortunately, to easily hide rows based on the value of a particular cell in a row. You can, however, achieve the same effect by using a macro to analyze the cell and adjust row height accordingly. The following macro will examine a particular cell in the first 100 rows of a worksheet, and then hide the row if the value in the cell is less than 5.

Sub HideRows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 3

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value < 5 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End Sub

You can modify the macro so that it checks a different beginning row, ending row, and column by simply changing the first three variables set in the macro. You can also easily change the value that is checked for within the For … Next loop.

You should note that this macro doesn’t unhide any rows, it simply hides them. If you are checking the contents of a cell that can change, you may want to modify the macro a bit so that it will either hide or unhide a row, as necessary. The following variation will do the trick:

Sub HURows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 3

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value < 5 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End Sub