Using a function in a set expression, what am I missing?

[Anonymous]

Hi all,

I'm experiencing a peculiar problem. I have selected a single line in my dataset

Consider this expression in a textbox:

=Avg({$<Onderwerp={'REK-3F-CE'}>}Cijfer)

It shows 3.01, just what i expect.

Now what I was trying to do is give the sheet(s) a contextrich name, REK-3F-CE in this case.

=MID(GetActiveSheetId(),10)

This one shows REK-3F-CE, just what i expect.

Next step:

=MID(GetActiveSheetId(),10) = 'REK-3F-CE'

returns -1 (TRUE), just what i expect.

Next step:

=MID(GetActiveSheetId(),10) = Onderwerp

also returns -1

But the final step:

=Avg({$<Onderwerp={"=MID(GetActiveSheetId(),10)"}>}Cijfer)

This one does not yield the result i want, I'd like it to return the same 3.01 as the first expression.

I was under the impression the function is executed, and the result used in the compare, but I suppose I am missing something here.

Is there anyone who can enlighten me?

Kind regards,

Herb

[sunny_talwar · Accepted solution]

I think you missed the dollar sign expansion:

=Avg({$<Onderwerp={"$(=Mid(GetActiveSheetId(),10))"}>} Cijfer)

[Anonymous]

Thanks! Works like a charm!

Do you by any chance know a web-place where i can brush up on dollar sign expansion rules/examples etc?

Kind regards, Herb

[sunny_talwar]

You can check this link out:

The Magic of Dollar Expansions

[Anonymous]

Thanks!