INDIRECT with named range

zJenkins

Board Regular
Joined
Jun 5, 2015
Messages
144
Hi,

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

Code:
=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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,760
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
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,760
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
 
Last edited:

zJenkins

Board Regular
Joined
Jun 5, 2015
Messages
144

ADVERTISEMENT

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]
  • DeptAccountJan
    0010690001=SUMIFS(INDIRECT("amt_"&C1),INDIRECT("dpt_"&C1),Main!$A2,INDIRECT("acct_"&C1),Main!$B2)

    <tbody>
    </tbody>
  • [GL_Jan]
  • DeptIDAccountTrans Amount
    00106900012500
    00106900013500
    00106900015000

    <tbody>
    </tbody>

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

Well-known Member
Joined
May 31, 2015
Messages
1,760
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

Board Regular
Joined
Jun 5, 2015
Messages
144

ADVERTISEMENT

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

Well-known Member
Joined
May 31, 2015
Messages
1,760
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

Board Regular
Joined
Jun 5, 2015
Messages
144
Yes, it does return the correct name:

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

Watch MrExcel Video

Forum statistics

Threads
1,108,507
Messages
5,523,310
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top