Forum Discussion
Possible to use one formula in multiple cells?
I have a workbook developed where the exact same formula is used in over 300 cells. I made the formula so that it acts 'by reference' (a lot of use of the INDIRECT() function) such that the formula doesn't need to change based on what cell is executing it. It is a fairly complicated formula that had to be copied and pasted over 300 times to deploy it. The cells are, for all intents and purposes, randomly located within sheets and across multiple sheets, so deploying an update to the formula is very time consuming. My question: is there a way that those 300+ cells can just reference one location to get the formula such that I only have to update the formula once if I need to make changes?
Much appreciate any ideas!
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.
3 Replies
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.
