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.