In the last lesson. Oops ..'ve taught ya ... mean that last post, we've learned how to create a formula for the journal, which is using VLOOKUP formula that takes data from a table in Sheet1 estimates (Estimate).
In the following section we will modify the formula so that it looks nicer when the journal. The modifications we can do is as follows:
1. In order for the debit journal slightly shifted to right
The formula we have created previously sbenarnya can now be used for the journal, but these estimates will be parallel to either debit or credit. This is the original formula:
= VLOOKUP (B6, Estimates! $ A $ 5: $ B $ 44,2,0)
Then between one and journals with other journals are separated by one line and writing # N / A. This is caused because the formula has been available but the code does not exist.
Now we can add it to the credit formula slightly shifted to the right, is as follows:
= IF (F6 = 0, VLOOKUP (B6, Estimates! $ A $ 5: $ B $ 44,2,0), "" & VLOOKUP (B6, Estimates! $ A $ 5: $ B $ 44,2,0))
The red is the formula enhancements, the purpose of the formula is as follows:
If the credit side there is no value then the usual formula (no shift to the right), in addition to the journal will be shifted to the right.
2. Eliminating the # N / A
To eliminate the # N / A, we add the formula as follows:
= IF (B6 = 0, "", IF (F6 = 0, VLOOKUP (B6, Estimates! $ A $ 5: $ B $ 44,2,0), "" & VLOOKUP (B6, Estimates! $ A $ 5: $ B $ 44 , 2.0)))
The red is the formula enhancements. The purpose of the formula is as follows:
If the code is not worth the journal no results are shown by double quotation marks in addition to no avail.
Let's see, between each transaction is separated by one row. But seenarnya in which there is a formula as indicated by red arrows.
3. Holding the title of the column when journal
Journal certainly made a lot so it will be sequential to the bottom line as much as desired. While the title shifted upward. This will make it difficult if not visible column headings.
To hold the title of the column, put kursosr in column A on line on the line below the date, if the example above fits Date 20-Jan-08. Then give the command Window, Freeze panes, it will come out elongated horizontal line between rows 5 and line 6. If you journal a long while down, hence the title of the column will remain visible.
Please note that designated by arrows. After the 5th row directly below the line to 10. This is because there is a hidden rows or rolled up.
good luck............
ReplyDelete