Help with INDIRECT syntax

DPO

Board Regular
Joined
Feb 20, 2007
Messages
131
A few days ago, Jonmo1 helped me with the INDIRECT function, which has been great. 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 someone could help. 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",""))))}
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
if cell A1 = "DPO" and B1 = A1 then Indirect(B1) will return DPO. indirect brings out the value(dpo) in the reference cited (b1) in the argument provided for indirect function. You have to rephrase your formula
ravi
 

DPO

Board Regular
Joined
Feb 20, 2007
Messages
131
hi, thanks for the prompt reply. i understand what you are saying about the value of a1 and b1, however, i don't follow what "rephrase" your formula means. what i am trying to do is be able to change the result set of my formulas by changing the reference values in A1 (as a variable). Does this make sense?
 

DPO

Board Regular
Joined
Feb 20, 2007
Messages
131
Hi Paddy D, Sorry about that, I was trying to reach out to Jonmo1 directly and post a general question in case I was unable to reach Jonmo1 as I have a deadline in the morning for what I'm working on. I'm new to the board, so I didn't mean to break any rules.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Etiquette help on cross-posting:

http://www.excelguru.ca/node/7

Stay in the same thread whenever possible. The point is that you don't want to have someone working hard on your solution when in another post it has already been answered.

Regards.
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
I am not with you this time. probably this is want you want
{=SUM(IF("GLAccountNumber"&$A$1&"Class"=indirect($A24))*(("GLClassNumber"&$A$1&"Class"=indirect(MID(G$3,6,4))),(("GLAccountBalance"&$A$1&"Class",""))))}
If I say Indirect (A24) , if A24 contains K5 then value of K5 is returned to equate with "GLAccountNumber"&$A$1&"Class"=
Will MID(G$3,6,4) return a cell reference?
I don't think expression INDIRECT("GLAccountBalance"&$A$1&"Class") can return a value.
If you can explain in simple terms what you want done, we can help you write the formula
Ravi
 

Forum statistics

Threads
1,181,056
Messages
5,927,864
Members
436,573
Latest member
CMR237

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