Named Ranges that refer to all of workbook

[dta1984]

I have a set of named ranges in a workbook. In the "refers to" dropdown, i selected workbook, which is what I want. In the actual formula for the range, it has just one of the tab names in the formula, leading me to believe that it really isn't referring to the entire workbook. Is there a way to change this?

Ex. ='2012'!$A$2:INDEX(!$A:$A,COUNTA('2012'!$A:$A))
my workbook has tabs for 2012, 2011, 2010 etc. I'd like the range to be the same for all of the workbook

[Whizbang]

I am not sure you are using the correct terms. The "Scope" is the one that has the dropdown to select Workbook, or individual sheets. This tells Excel where this named range is visible. Workbook means that you can use that name in a formula anywhere in the workbook. If you specify a sheet, then that named range is only available for use in that sheet.

The refers to is the formula. The sheet name will automatically be entered into the formula. Maybe there is a way to remove the sheet name, but I don't know how. You can make the references relative (eg A1 instead of $A$1), which means that the formula will adjust depending on the cell using the named range. But, as far as I know, you can't make the sheet relative.

Of course, now that I have said it can't be done, someone will post a way.

[Whizbang]

I found a workaround.

http://www.bettersolutions.com/excel...U822216331.htm

The link above says to use Offset, but I found that all you need is the ! with no sheet name. =!A1 will refer to A1 on the active sheet.

The below works just fine.
=!$A$1:INDEX(!$A:$A,COUNTA(!$A:$A))

[OnErrorGoto0]

Named ranges that are local to a sheet can be used in other sheets, you just need to prefix the range name with the sheet name.

I would advise against using the !address syntax - it is extremely unreliable in my experience. It would be better to use INDIRECT in the name definition if you want it to refer to the same thing on each sheet.

[Whizbang]

In what way is it unreliable?

[OnErrorGoto0]

In terms of not updating. To be fair, it has been many years since I abandoned it so it may have been fixed by now.

[jeffreybrown]

Whizbang,

Very nice. This fits into something I can use which has been bugging me. Thanks for sharing

[dta1984]

OK, you're right, i had the term wrong.

What i was confused on is why when I select "scope" to be workbook, does the formula still have the sheet name in it. Ex. when I click on a tab that is 2011 in my workbook, my named ranges do not work on this sheet for some reason. Can i just re-enter them for each sheet? Just seems counter -productive.

I will try the method below using the !

[OnErrorGoto0]

See also Charley Kyd's comment about half-way down this page about crashing Excel. I am also 90+% certain I have seen Jan Karel Pieterse warn against using it too.

[Whizbang]

Well, I'll try it out in a few workbooks and scenarios and see how it behaves in 2010. So far, using the formula "=!$A$1:INDEX(!$A:$A,COUNTA(!$A:$A)) " in a named range called "Test", then using "=Counta(Test)", updates fine when I fill down the column.

Also, this appears to work fine:

Please Login or Register  to view this content.

When I add sheets, both of the above continue to function fine.

If you have any other resources commenting on the behavior of ![Ref], I would love to see them. In the mean time, I will use it where I feel appropriate, or can anticipate a likely use, and report back with any issues I have.

[dta1984]

OK so here's a little background on my problem; I have a workbook i created, and it references another workbook on our network. Yesterday a new "upated" workbook got released that has 2012 info in it. All of my formulas now change in the book i created. I used the "change source" option on the edit links dialog box, and got the proper naming in all of my formulas now. Only problem is they are all showing #NAME errors. Now....when I change my name manager in the ref workbook to the !A2 structure, my errors go to #REF. Everything worked fine until I changed to the new ref workbook.

I am almost certain my errors are coming from the naming structure for the columns. I have data that needs to pull from a 2011 tab in the ref book (so it is stored for record keeping), but I also need new data pulled from the 2012 tab. My formulas are all SUMPRODUCT.

Here is an ex.
=SUMPRODUCT(--('PURCHASE RECEIPTS FILE (2009_2012).2012.01.03.xlsx'!VENDOR_NAME="XXXX"),--('PURCHASE RECEIPTS FILE (2009_2012).2012.01.03.xlsx'!DATE>='DO NOT DELETE'!L$1),--('PURCHASE RECEIPTS FILE (2009_2012).2012.01.03.xlsx'!DATE<='DO NOT DELETE'!L$2),--('PURCHASE RECEIPTS FILE (2009_2012).2012.01.03.xlsx'!PO>=$E22),--('PURCHASE RECEIPTS FILE (2009_2012).2012.01.03.xlsx'!PART_NO.=$A22),'PURCHASE RECEIPTS FILE (2009_2012).2012.01.03.xlsx'!QTY)

[Whizbang]

I do not know if the error is the same, but I recently helped someone who was trying to use Dynamic Named Ranges (using Index) in a SUMPRODUCT formula that referenced a closed workbook. For whatever reason, the Dynamic Named Range part didn't calculate and returned an error. Try changing the formula in the named range to a fixed range and see if your formula calculates.

[OnErrorGoto0]

Re the !address issue, see the article here for instance. Up to you if you wish to use it.

Edit: I just ran a quick test in 2010 and a recalculate does indeed cause all cells using the name to recalculate based on the active sheet, rather than the sheet they are on.

[dta1984]

I do not know if the error is the same, but I recently helped someone who was trying to use Dynamic Named Ranges (using Index) in a SUMPRODUCT formula that referenced a closed workbook. For whatever reason, the Dynamic Named Range part didn't calculate and returned an error. Try changing the formula in the named range to a fixed range and see if your formula calculates.

Hmm, so if I add in the sheet name to the formula after the file name, it works now. I also separated the name manager names and now have 1 for each tab instead of trying to do it by the whole workbook.

ex. SUMPRODUCT(--('[PURCHASE RECEIPTS FILE (2009_2012).2012.01.03.xlsx]2011'!DATE>='DO NOT DELETE'!E$1)