Referencing Named Ranges from Add-Ins

[Darren Arnold]

I've made an Add-In to hold all my formulas for, say brick coursing.

I then made a name range in the Add-In to contain the names and dimensions of all brick types.

So far so good.

I make a new spreadsheet, enable the add-in, the functions work, except...

I click on a cell, enable data validation, and try to reference the named range in the add-in... I can't.

So in a nutshell:

If I make a named range in an add-in, how do I reference it in a sheet that uses it?

How to I make =NamedRangeFromAddIn work?

 

[Jon Peltier]

Here's how to find the max value in such a range:

=MAX([addin_name.xls]sheetname!rangename)

 

[Darren Arnold]

But if the sheet is an add-in and not an actual sheet will it still work?

I'm not distributing the sheet, only the add-in

 

[Jon Peltier]

Did you try it?

If you know the filename, sheet name, and range name, it will work.

 

[Darren Arnold]

I did try it but it doesn't want to call a named range from the Add-In.

Every time I tried to reference the range it wouldn't accept [Filename]SheetName!NamedRange.xlam

Nor would the Data Validation accept a named range from another sheet even when it wasn't an add-in

It can call from sheets, but won't recongnise named ranges in the add-ins

 

[Jon Peltier]

".xlam" is part of the file name:

[Filename.xlam]SheetName!NamedRange

But Data Validation only accepts a range name that's defined in the workbook that contains the data validation. You could probably define a name in the workbook that refers to the name in the add-in, and use that for the DV list.

 

[Darren Arnold]

Thanks, Jon

It didn't work for me at work, but it did work at home so I think I must have some setting or location screwed up somewhere.

Thanks for reiterating the answer because I nearly gave up on it.

The other part was the Data Validation would only accept a local name so all I did was make a hand-hold local name.

Again, thanks.