INDIRECT and named table range combo?

**nms2130** · 07-11-2008, 10:04 AM

I know how to use the indirect function and how to name table ranges, but is it possible to combine these two? Because in defining the table range, the tab/worksheet name is included. If the range is the same on each tab, is it possible to create a named range that encompasses the range on select sheets?

This is what I am using now:

IF(ISERROR(HLOOKUP($A3,INDIRECT("'[nms2130.xls]" & $B3 & "'!9:11"),2,FALSE)),HLOOKUP($A3,INDIRECT("'[nms2130.xls]" & $B3 & "'!42:46"),2,FALSE),HLOOKUP($A3,INDIRECT("'[nms2130.xls]" & $B3 & "'!9:11"),2,FALSE))

$B3 is the tab name, which changes.
The problem is that when a new row is added, the formula continues to search in ranges 9:11 & 42:46 instead of 9:12 & 42:48(because the row is also added in between 42:46).

I have the same problem in the formula below:
IF(($A$2="o")*(INDEX(INDIRECT("'[nms2130.xls]" & $A$1 & "'!$c$27:$iv$27"),1,ROWS($4:4))>0),INDEX(INDIRECT("'[nms2130.xls]" & $A$1 & "'!$c$9:$iv$9"),1,ROWS($4:4)),IF(($A$2="c")*(INDEX(INDIRECT("'[nms2130.xls]" & $A$1 & "'!$c$27:$iv$27"),1,ROWS($4:4))<0),INDEX(INDIRECT("'[nms2130.xls]" & $A$1 & "'!$c$9:$iv$9"),1,ROWS($4:4))," "))

Any help would be greatly appreciated.

**NBVC** · 07-11-2008, 10:17 AM

If you put the name of the named ranged in a cell, then you can use the indirect to reference the named range tooo..

eg.

HLOOKUP($A3,INDIRECT("'[nms2130.xls]" & $B3 & "'!" & $B$4),2,FALSE)

where B4 would contain the name of the named range...

**nms2130** · 07-11-2008, 10:22 AM

Then if I have 17 tabs will I have to create 17 table names?

Can I use a dynamic table name like the following?

OFFSET($A$6,0,0,COUNTA($A$6:$A$60000),2)

Changing to rows, rather than columns?

**NBVC** · 07-11-2008, 10:40 AM

I'm not exactly sure that I follow, but when you used named ranges, I think you have to (or it will default) define a sheet name within the fomrula....

so your dynamic range will change to something like =OFFSET(Sheet1!$A$6,0,0,2,COUNTA(Sheet1!$A$6:$Z$6))

where Sheet1 is inserted automatically if you don't insert a sheetname yourself.

**nms2130** · 07-11-2008, 12:19 PM

Right. The table name range inserts the current sheet anme into the formula. Can an indirect be used in the define table name area for the sheet?

Refers to:

=indirect function combined with dynamic offset

any ideas?

**nms2130** · 07-11-2008, 01:27 PM

This is what I have come up with...but it is returning an error.

INDIRECT("'[nms2130.xls]" & $c$4 & "'!OFFSET(INDIRECT("'[nms2130.xls]" & $C$4 & "'!$c$27:$iv$27",0,0,COUNTA(INDIRECT("'[nms2130.xls]" & $C$4 & "'!$c$27:$iv$27"),256)

I am placing the tab name in c4. I can't seem to find what I am doing wrong.