Convert a string to a Range??

[websmythe]

Hi, I'm working in Excel 2003. Is there a way to convert strings to Ranges on the Excel commandline?

I have a lookup function in an estimate sheet that uses 2 ranges, stored as strings, in another worksheet. They are used to return a price from another workbook. When I use named ranges to access them with the lookup function, it doesn't work. But when I cut and paste the strings into the commandline it works prefectly??

I'm stumped... I've looked everywhere I can think of, could anyone offer some suggestions? Thanx.

$A10 = Part Number to search for
 
MaterialOptions is a named Range 
eg: '[Catalog.xls]PriceList'!$A$13:$A$17
 
PriceOptions is a named Range  
eg: '[Catalog.xls]PriceList'!$Z$13:$Z$17
 
LOOKUP($A10,MaterialOptions,PriceOptions) = #VALUE!
But...
LOOKUP($A10,'[Catalog.xls]PriceList'!$A$13:$A$17,'[Catalog.xls]PriceList'!$Z$13:$Z$17) = Success!

 

[Andrew Poulsom]

Are your names defined in the workbook containing the formula? I can't reproduce your problem if they are.

 

[Aladin Akyurek]

Are your names defined in the workbook containing the formula? I can't reproduce your problem if they are.

If it's a dynamic named range and the target book is closed, a #VALUE! would follow...

 

[Andrew Poulsom]

If it's a dynamic named range and the target book is closed, a #VALUE! would follow...

Yes, but the OP made no mention of dynamic ranges. The references given are static.

 

[websmythe]

Are your names defined in the workbook containing the formula?

Yes, and there are static.

I tryed to insert a snapshot from HTMLmaker, but it failed. Will try and post an image.

 

[websmythe]

Thanx for the reply.
Hope this image isnt too convoluted.

http://img9.imageshack.us/img9/8268/failedv.jpg

Cell G14 is totally successful,
but, has the addresses from J14 and K14 pasted into the commandline,
so I fgure they are behaving like ranges

Cell G12 is the culprit.
So only thing I can figure is that J12 & K12 are not being treated as ranges, so it doesnt want to go get the data.

 

[websmythe]

Oh ya, as I was saying... as you can see
J12, K12, J14 & K14 are Ranges for Material Options and Price Options
(from a separate "Catalog" Workbook) which are stored in the Items worksheet

Probably, not the most elegant way to handle things,
but hey, I can learn

 

[websmythe]

In trying to solve this problem using VBA, even tho Help says that
"LOOKUP, VLOOKUP & HLOOKUP are available to VBA",
when I try to use them I get a "Compile Error: Sub or Function not defined".
Is that related to the issue??

 

[Andrew Poulsom]

To use thos functions in VBA you have to qualify them with their parent, the WorksheetFunction object. Example:

x = WorksheetFunction.VLookup("Range("A1").Value, Range("B1:C100"), 2, False)

 

[websmythe]

Thanks for that.

Got the function to work in VBA,
except it's still only returning a string like it did on the formula bar...
eg: '[Catalog.xls]Summary'!$Z$13:$Z$17 ...which happens to be the same as the ResultVector
...instead of the cell value Im trying to lookup.

Definately a step in the right direction tho, cause at least it's returning something
I think it must be the way I'm trying to use the reference. Maybe cause its in another workbook??

Function GetPrice(LookupVal, LookupRng, ResultRng)
 
' Get lookup ranges from the Items worksheet sheet
' and use them to get the respective price from the external Catalog workbook
 
    Set LookupVector = LookupRng    ' make sure its a range
    Set ResultVector = ResultRng    ' make sure its a range
 
    GetPrice = WorksheetFunction.Lookup(LookupVal, LookupVector, ResultVector)
 
End Function