Microsoft Office Tutorials and References
In Depth Information
B11: =MATCH(A11,ActGL.xls!Name,0)
If the Name range in the ActGL.xls worksheet doesn’t contain the
text found in cell A11, this formula returns #N/A.
On the other hand, if the Name range contains more than one cell
with the text found in cell A11, this formula returns the first instance
found. Therefore, you need to ensure that there are no duplicate
names in the Name range of your data workbooks.
The Sign Value
Cell C11 in Figure 8-5 returns the value for the default sign for the
account in the General Ledger. Accounts with a normal debit
balance have a Sign of 1; accounts with a normal credit balance
have a Sign of -1.
Here’s the formula for the cell shown, which returns this value from
the database:
C11: =INDEX(ActGL.xls!Sign,B11)
The Data Value
Now that our worksheet contains the key values needed, we can
enter a formula that returns the value for the specified account in
the specified month:
D11: =INDEX(ActGL.xls!DataMonth,$B11,D$10)*$C11
Here, the INDEX function returns data from the DataMonth range,
from the row specified by cell B11 and the column specified by cell
D10. This value is multiplied by the Sign value fund in cell C11.
Copy this formula to the right, as needed.
Search JabSto ::




Custom Search