deLockloire
Board Regular
- Joined
- Apr 4, 2008
- Messages
- 110
- Office Version
- 365
- Platform
- Windows
Hey,
I have a couple of named functions (e.g., functions I entered the name manager, gave them names and can not be invoked by entering the names). E.g., two of the named functions are: "exr_average" and "exr_latest" and they return either the latest exchange rate of a selected currency or its average rate in a selected period. Now there is a data validation selector for the currency, the period (with a starting and end date) and the mode of calculation (latest or average).
What I'd like is to construct the string with INDIRECT() and have those named functions work. However, now it does not, and I am unsure if it should or not. Should it? E.g., instead of writing an IF function like this:
I'd like to simply construct the name with INDIRECT() like this:
Which would execute the exr_average named function if "Average" is selected and exr_latest is "Latest" is selected.
The reason why such a solution would be preferable is because there are more variables at play than I am telling here and then I could avoid long and complicated embedded IFs. Basically the question is if this should at all, in theory, work or not?
Regards,
deL
I have a couple of named functions (e.g., functions I entered the name manager, gave them names and can not be invoked by entering the names). E.g., two of the named functions are: "exr_average" and "exr_latest" and they return either the latest exchange rate of a selected currency or its average rate in a selected period. Now there is a data validation selector for the currency, the period (with a starting and end date) and the mode of calculation (latest or average).
What I'd like is to construct the string with INDIRECT() and have those named functions work. However, now it does not, and I am unsure if it should or not. Should it? E.g., instead of writing an IF function like this:
VBA Code:
=IF(MODE_OF_CALCULATION="AVERAGE", exr_average, exr_latest)I'd like to simply construct the name with INDIRECT() like this:
Code:
=INDIRECT("exr_"&MODE_OF_CALCULATION)Which would execute the exr_average named function if "Average" is selected and exr_latest is "Latest" is selected.
The reason why such a solution would be preferable is because there are more variables at play than I am telling here and then I could avoid long and complicated embedded IFs. Basically the question is if this should at all, in theory, work or not?
Regards,
deL
Thanks!
