I tested your formula and your syntax seems fine.  Do you get an error message or just an incorrect answer.  If error message, what error do you get?  
#Ref would indicate that your cell A1 has additional spaces or something other than "Q_1"  
#N/A would indicate that your lookup value is outside of the range

Patrick,

The problem might be the dynamic range. They have some special qualities that make them a bit more difficult to deal with. What is the name reference? You might also try setting Q_1_Scale to a static range and see if the results improve.

Kevin

ImageryGrl,

It's a #REF error.

Regards,

Patrick

Kevin,

It appears that I am being done in by those "special qualities".  When I make Q_1_Scale static, the INDIRECT() approach
works just fine.

Overall, it's OK--it would have made my current workbook project a tiny bit more convenient to use INDIRECT with the
dynamic ranges, but my alternate VLOOKUP() construction will do the job.

I'll leave this open a bit longer, to see if Brad, Dave, Matt, or someone else has anything to offer...

Regards,

Patrick

Patrick,

I just got the same exact results as you with the same tests. I'll see if I can come up with something for you.

A question: why do you need the name to be dynamic?

Kevin

Kevin,

It's a long story, but I'll try to be brief :)

I am on a project where a client has lots of different operations stats being compiled, and each stat has its own scaling
and scoring rules.  The scoring rules are stored in a big lookup table, and instead of having different tables for each
stat, all the stats' rules are in a single lookup table.  I am using an ID string to identify the rules for any given stat.

So, for example, Q_1 is simply the first "quality" stat being tracked, and the lookup table will have n records saying
how to score for different values of the stat.

Since the scoring rules are expected to change often over the next 12 months, I figured this construction would make
it easier to do updates.

The stats themselves are being computed on individual worksheets, with some commonality across all the worksheets.
I was hoping to have the formulae on the various worksheets that handle the scoring calc to look the same everywhere,
and just have A1 on each worksheet hold the stat ID.

Confused yet? :)

Regards,

Patrick

And, since the stats are in one table, you need to bound the search area from the top and the bottom...now I understand.

Kevin

Kevin,

You got it.

Regards,

Patrick

Kevin,

You are the man!

I had to make a slight tweak, changing:

   Row = Application.Match(SearchValue, Range(SourceRange).Columns(1), 0)

to:

   Row = Application.Match(SearchValue, Range(SourceRange).Columns(1), 1)

I did this because I need to emulate VLOOKUP's "close match" capability.

Thanks a bunch!

Regards,

Patrick

Kevin,

I guess for posterity the function could have an optional fourth argument that controls how MATCH will operate...

Regards,

Patrick

I'm glad you're happy. Not often one gets to help a fellow expert. :-)

Kevin

Kevin,

For the record, here is the function in the form I implemented in my project:

Public Function VLookupSpecial(ByVal SearchValue As Variant, ByVal SourceRange As String, _
    ByVal ColumnOffset As Long, Optional ByVal MatchType As Long = 0) As Variant

    Dim Row As Long
         
    On Error Resume Next
    Row = Application.Match(SearchValue, Range(SourceRange).Columns(1), MatchType)
    On Error GoTo 0
    If Row > 0 Then
        VLookupSpecial = Range(SourceRange).Columns(ColumnOffset).Cells(Row).Value
    Else
        VLookupSpecial = CVErr(xlErrNA)
    End If

End Function



In my formulae, I am using 1 for the optional fourth argument.

Pleased as punch,

Patrick

Kevin,

This issue came up in VBAX, in case you are interested:
http://www.vbaexpress.com/forum/showpost.php?p=25408&postcount=48

Regards,

Patrick

Patrick,

XLD does not have it quite right. It is not what the OFFSET or INDIRECT functions return but rather what the named range mechanism returns. Consider this example:

Cell A1 contains the string "B1:B10" and the cells B1:B10 contain the letters a through j. There is a named range, "Test" that contains the following formula:

=OFFSET(Sheet1!A1,0,0)

Cell C1 contains:

=MATCH("i",INDIRECT(OFFSET(A1,0,0)),0)

Cell C2 contains:

=MATCH("i",INDIRECT(Test),0)

Cell C1 resolves to 9 while C2 resolves to #REF!

As you can see the ONLY difference between cells C1 and C2 is the indirection of the OFFSET function behind the name. Thus we can conclude that the name resolution function is the culprit and not the OFFSET or INDIRECT function. In other words, don't expect dynamic range names to ever return a range reference, they will always return values. Then the question becomes: What does the next function in line expect? VLOOKUP and MATCH for example insist on a range reference which is why they fail. SUM on the hand will accept either a range reference or values.

Kevin