ja-rek
asked on
substitute parameters with values in Excel formula
Dear Experts,
I am looking for a function (preferably) that would do the following:
- input: formula (string) or reference to a cell containing formula
- output: input formula (converted to string) but with parameters substituted with values
let's say I have a formula:
=sum(a1;a3;a5)
and cell a1=1, a3=3, a5=5
then the result should be =sum(1;3;5)
if the parameter is a range like in vlookup function, for example:
=vlookup(a1;a:a;3,0)
since second parameter is a range then it should remain unchanged.
the sample result should be in this case like:
=vlookup("pink";a:a;3,0)
is there a way (using VBA?) to achieve it in a relatively simple way?
thank you
Jarek
I am looking for a function (preferably) that would do the following:
- input: formula (string) or reference to a cell containing formula
- output: input formula (converted to string) but with parameters substituted with values
let's say I have a formula:
=sum(a1;a3;a5)
and cell a1=1, a3=3, a5=5
then the result should be =sum(1;3;5)
if the parameter is a range like in vlookup function, for example:
=vlookup(a1;a:a;3,0)
since second parameter is a range then it should remain unchanged.
the sample result should be in this case like:
=vlookup("pink";a:a;3,0)
is there a way (using VBA?) to achieve it in a relatively simple way?
thank you
Jarek
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ill run some tests using the () and commas as separators unless someone comes up with a better solution.