Hi. I am hoping someone will be able to assist me with the syntax for a formula I'm working on? The array formula itself is relatively simple (below).
{=SUM(IF(GLAccountNumber4500Class=$B12,GLAccountBalance4500Class,""))}
The defined names in the formula reference the following:
GLAccountNumber4500Class - General ledger account numbers on a separate tab named 4500
GLAccountBalance4500Class - General ledger account balances on the same separate tab named 4500
As you can guess, the value in cell $B12 will be the general ledger account # to be searched and matched.
The source data is loaded on a separate tab from where the formula resides (there are multiple separate tabs for with similar data sets which need to be sourced/referenced). The challenge is to have the formula change the tab/defined name reference based upon another field (i.e. E12). In other words, if the user selects 4501 from a drop down box in field E12, I need the formula to change the '4500' reference in the formula's defined name dynamically based upon the fund number value selected in E4. For example, if the user selects '4501' from the drop down list in E12, the formula should now read:
{=SUM(IF(GLAccountNumber4501Class=$B12,GLAccountBalance4501Class,""))}
I could change this reference via a macro, however, I was hoping to avoid a macro and do this within the formula. There are too many variations to use an If formula and hard code in the fund numbers into the formula.
Please let me know if you need any further clarification. Any help is greatly appreciated.
{=SUM(IF(GLAccountNumber4500Class=$B12,GLAccountBalance4500Class,""))}
The defined names in the formula reference the following:
GLAccountNumber4500Class - General ledger account numbers on a separate tab named 4500
GLAccountBalance4500Class - General ledger account balances on the same separate tab named 4500
As you can guess, the value in cell $B12 will be the general ledger account # to be searched and matched.
The source data is loaded on a separate tab from where the formula resides (there are multiple separate tabs for with similar data sets which need to be sourced/referenced). The challenge is to have the formula change the tab/defined name reference based upon another field (i.e. E12). In other words, if the user selects 4501 from a drop down box in field E12, I need the formula to change the '4500' reference in the formula's defined name dynamically based upon the fund number value selected in E4. For example, if the user selects '4501' from the drop down list in E12, the formula should now read:
{=SUM(IF(GLAccountNumber4501Class=$B12,GLAccountBalance4501Class,""))}
I could change this reference via a macro, however, I was hoping to avoid a macro and do this within the formula. There are too many variations to use an If formula and hard code in the fund numbers into the formula.
Please let me know if you need any further clarification. Any help is greatly appreciated.