INDIRECT with named range

[zJenkins]

Hi,

I'm trying to reference a named range using the indirect formula but am getting an #REF error.

=IF(cur_month>=G$12-1,SUMIFS(INDIRECT("amt_"&G13),INDIRECT("dpt_"&G13),'Rooms-0010'!$A32,INDIRECT("acct_"&G13),'Rooms-0010'!$B32),SUM(G15:G31))

cell G13 has the value of "Jan" and my named ranges are "amt_Jan", dpt_Jan, and "acct_Jan"

The formula works until I add the INDIRECT's to it.

Thank you for your help.

 

[Jeffrey Mahoney]

I don't think it's related to the INDIRECT part.

Are all of your ranges: Amt_jan, dpt_jan, and Acct_jan the same size?

Jeff

 

[zJenkins]

Are all of your ranges: Amt_jan, dpt_jan, and Acct_jan the same size?

Yes, the ranges are the same size. Not sure if it matters, but fyi they are dynamic ranges.

 

[Jeffrey Mahoney]

Nope, them being dynamic doesn't have an affect unless they are the error.

* Try using a simple formula to count the values in the dynamic ranges to make sure they are valid and to make sure they all have the same count.
* Try typing the name of the dynamic range into the formula without referencing cell G13

If those don't show any errors, maybe post some data from the ranges so we can try to reproduce your problem. Maybe post the formula you use for the dynamic ranges.

Jeff

 

[zJenkins]

Thanks Jeff,

The formula works just fine if I simply reference the named ranges instead of using INDIRECT formula, so no issue there. I guess I'm unable to attach a workbook, so here's a basic outline of how it's set up:

2 worksheets

• [Main]
• [TABLE="width: 500"]
  <tbody>[TR]
  [TD]Dept[/TD]
  [TD]Account[/TD]
  [TD]Jan[/TD]
  [/TR]
  [TR]
  [TD]0010[/TD]
  [TD]690001[/TD]
  [TD]=SUMIFS(INDIRECT("amt_"&C1),INDIRECT("dpt_"&C1),Main!$A2,INDIRECT("acct_"&C1),Main!$B2)[/TD]
  [/TR]
  </tbody>[/TABLE]
• [GL_Jan]
• [TABLE="width: 500"]
  <tbody>[TR]
  [TD]DeptID[/TD]
  [TD]Account[/TD]
  [TD]Trans Amount[/TD]
  [/TR]
  [TR]
  [TD]0010[/TD]
  [TD]690001[/TD]
  [TD]2500[/TD]
  [/TR]
  [TR]
  [TD]0010[/TD]
  [TD]690001[/TD]
  [TD]3500[/TD]
  [/TR]
  [TR]
  [TD]0010[/TD]
  [TD]690001[/TD]
  [TD]5000[/TD]
  [/TR]
  </tbody>[/TABLE]

Named ranges:

• acct_Jan => =OFFSET(GL_Jan!$B$2,0,0,COUNTA(GL_Jan!$B:$B),1)
• amt_Jan => =OFFSET(GL_Jan!$C$2,0,0,COUNTA(GL_Jan!$C:$C),1)
• dpt_Jan => =OFFSET(GL_Jan!$A$2,0,0,COUNTA(GL_Jan!$A:$A),1)

 

[Jeffrey Mahoney]

In this formula:
=SUMIFS(INDIRECT("amt_"&C1),INDIRECT("dpt_"&C1),Main!$A2,INDIRECT("acct_"&C1),Main!$B2)
does cell C1 contain text, or is it a date formatted as a 3 letter month abbreviation?

 

[zJenkins]

In this formula:
=SUMIFS(INDIRECT("amt_"&C1),INDIRECT("dpt_"&C1),Main!$A2,INDIRECT("acct_"&C1),Main!$B2)
does cell C1 contain text, or is it a date formatted as a 3 letter month abbreviation?

It's formatted as text

 

[Jeffrey Mahoney]

Try this:

Edit the formula highlight the section in I have in Red, and press F9 (function key). It should change to show what those two text values merged. Is it correct; is it the name of the dynamic range?

=SUMIFS(INDIRECT("amt_"&C1),INDIRECT("dpt_"&C1),Main!$A2,INDIRECT("acct_"&C1),Main!$B2)

 

[zJenkins]

Yes, it does return the correct name:

=SUMIFS(INDIRECT("amt_Jan"),INDIRECT("dpt_Jan"),Main!$A2,INDIRECT("acct_Jan"),Main!$B2)

 

[Jeffrey Mahoney]

Scratching head... ... ...