Dynamic Defined Name

[DPO]

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.

 

[Jonmo1]

Welcome to the board...

1. This formula doesn't need to be so complicated, as an array..
this should be sufficient...

=SUMIF(GLAccountNumber4500Class,$B12,GLAccountBalance4500Class)

2. Yes, you can vary the Tabname using Indirect.. However this will decrease the performance. If you use Indirect alot, you will see the performance of your sheet go down...But for just 1 formula, shouldn't be a big deal.

=SUMIF(INDIRECT("GLAccountNumber" & E12 & "Class"),$B12,INDIRECT("GLAccountBalance" & E12 & "Class"))

 

[DPO]

Beautiful! Thank you VERY much! A few comments: I had simplified the formula to one search criteria for purposes of the example, however, there formula will become increasingly complex now that I can do this (therefore, the array). I had tried to concatenate the values with "" and & l earlier, however, it was the INDIRECT function which I was missing. I had read on other posts about INDIRECT, however, you explained it much more clearly, and therefore, I was able to apply it. Thank you again, this is fantastic!!

 

[DPO]

Hello again. I have used the INDIRECT function quite a bit on the workbook I am creating, however, I am having difficulty with one of them and was hoping you could help again. Here is what I have (I'm thinking that I'm not grouping/closing the parentheses correctly):
{=SUM(IF((INDIRECT("GLAccountNumber"&$A$1&"Class"=$A24))*(INDIRECT("GLClassNumber"&$A$1&"Class"=MID(G$3,6,4))),(INDIRECT("GLAccountBalance"&$A$1&"Class",""))))}

 

[Jonmo1]

Yes, I think some misplaced parentheses...
try

{=SUM(IF((INDIRECT("GLAccountNumber"&$A$1&"Class")=$A24)*(INDIRECT("GLClassNumber"&$A$1&"Class")=MID(G$3,6,4)),(INDIRECT("GLAccountBalance"&$A$1&"Class"),"")))}