Accessing Excel Named Ranges

PowerShell Help
1

I use PowerShell to access a series of Excel workbooks and pull data from them. All the examples I have found to extract data from Excel Named Ranges requires that I know the worksheet that the Named Range is on such as:

$WorkSheets = $WorkBook.Worksheets |where {$_.name -eq "Property Summary"}
    if ($worksheets){
...
...
$Details = [pscustomobject]@{
Address = $WorkSheet.Range('Address').Text
City = $WorkSheet.Range('CityStateZip').Text
...
...
}
$Details | Export-Csv -path .....
}
else {}
...
...

This sorta defeats the purpose of a Named Range in that the Named Ranges should be allowed to be anywhere in the workbook.

Is it that there is no way around this or did all the examples happen to use the Worksheet/cell address and Named Range interchangeably and I’m just not finding the right solution?

I have some versions of these worksheets where the Named Range value is on a specific worksheet in one “version” of the workbook and it’s on a different worksheet in other versions so I’m having to code around this with If/Then loops to try to identify the particular version of worksheet. The more things change, like someone renaming a worksheet, the harder this becomes.