Forum Discussion
Possible to use one formula in multiple cells?
This is exactly what LAMBDA function is ideal for. basically in the Name Manager you would create a new NAME and the definition would be like:
=LAMBDA(a, b, c, 100*a+IF(b>c, b, c) )
then if that new NAME was called MyFunct then in each of the cells you just enter:
=MyFunct( A10, B33, C22 )
like any other function
Hopefully you can get rid of all those INDIRECT() calls as they are "volatile" and can cause performance issues.
This is exactly what LAMBDA function is ideal for. basically in the Name Manager you would create a new NAME and the definition would be like:
=LAMBDA(a, b, c, 100*a+IF(b>c, b, c) )
then if that new NAME was called MyFunct then in each of the cells you just enter:
=MyFunct( A10, B33, C22 )
like any other function
Hopefully you can get rid of all those INDIRECT() calls as they are "volatile" and can cause performance issues.
Thanks much! That did the trick perfectly, not sure how I never came across the Lambda feature before, thanks for introducing me to it! Definitely going to have more use for it now that I know it exists.
In my use case, the INDIRECT is necessary due to the dynamic range of which the formula is pulling data. But I have it pretty locked down and backstopped from things like circular references. I haven't had any instability issues, but your point is taken, it's something that has to have care in using or tends to be a dragon eating its own tail.
Thanks again!
I'm glad it worked for you and it is great. I is relatively new (to us old timers) and was introduce around 2020.
As for INDIRECT, yes circular ref can be a problem but by 'volatile' what that means is that Excel has to re-calculate that cell every time anything in excel changes. Usually Excel is smart and will only calculate cell that might be affected (e.g. you change A1 and B1=A1+5 then it knows to update B1). So if you have a lot of INDIRECTs it can impact excel performance because it has to do all those calculations every time. There are very few cases where I can't find a workaround to avoid using INDIRECT so it is just something to keep in mind.