Dynamic Defined Name

DPO

Board Regular
Joined
Feb 20, 2007
Messages
131
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

1. This formula doesn't need to be so complicated, as an array..
this should be sufficient...
Code:
=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.

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

DPO

Board Regular
Joined
Feb 20, 2007
Messages
131
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

Board Regular
Joined
Feb 20, 2007
Messages
131
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Yes, I think some misplaced parentheses...
try
Code:
{=SUM(IF((INDIRECT("GLAccountNumber"&$A$1&"Class")=$A24)*(INDIRECT("GLClassNumber"&$A$1&"Class")=MID(G$3,6,4)),(INDIRECT("GLAccountBalance"&$A$1&"Class"),"")))}
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top