I'm trying to do a conditional count "COUNTIF" from closed workbook named range. Like the example below, count the amount of "fruit_count" if the "fruit_names" is "apples":


I've found the getvalue() formula but it looks like doesn't work on a named ranges:

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function  

The perfect would be to create a function that permits to be executed on the the excel ribbon formula bar but the "ExecuteExcel4Macro" doesn't allow that, or a way to combine the getvalue function with named ranges...

I saw many issues and questions about this topic but not many answers with named ranges.

Thanks in advance, av9


That's because you don't reference a sheet when you reference a named range, the Name of the range should be enough. To do that your arg variable should look something like 'C:\folder\thisworkbook.xlsx'!this_range or 'C:\folder\[Someotherfile.xlsx]'!that_range for named ranges to work.

Now you are feeding ExecuteExcel4Macro() with a wrong string, that's why this function breaks.


GetValue(path, file, sheet, ref)

Should be this

GetValue(path, file, named_range)

And this

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

to this

arg = "'" & path & "[" & file & "]" & "'!" & named_range
arg = "'" & path & "\" & file & "'!" & named_range

This way it will return you range. I've tested it.


| improve this answer | |
  • Nice! By the time, all this thing about the "GetValue" started because the function INDIRECT doesn't work when refering to a closed workbook, I've found INDIRECT.EXT but this one doesn't work with named ranges so... Do you know if there's any other way to use a kind of INDIRECT function with named ranges? The reason of the INDIRECT is just for select the workbook path via cell value. Or, Would be possible to modify the GetValue for avoiding the "ExcecuteExcel4Macro" function and then place the function on the Excel formula bar? Thanks dmb! – AV9 Feb 13 '19 at 14:19
  • Yes, you can use indirect with named ranges, but it must contain only one cell. This is because, as it says here than INDIRECTrecieves a cell reference(just one) and returns whats in there. Just name one cell and use indirect with that name between " – dmb Feb 13 '19 at 14:22
  • But then I can't use any "sumproduct" function for example for counting values from a named range... Isn't it? – AV9 Feb 13 '19 at 14:30
  • Yes, you can but it's not at all clear what you are trying to do. If you do =count('C:\somepath\somebook'!range_with_name) it will return the counted numbers in said range. If you do =count.if('C:\somepath\somebook'!range_with_name; indirect(one_cell_named_range)) will count anything in range_with_name thats like what's in one_cell_named_range. The thing is I don't get why you want to use indirect if you only want to use sumif – dmb Feb 13 '19 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.