# Formula Length Limitation

1. ## Formula Length Limitation

Hi all!

I have been struggling with a problem of overcoming formula length limitation. My problem is as follows:

I have 4 parameters with several values for each in "Scenario Control" sheet. First parameter represents 3 cost scenarios. Other three parameters define another 10 different cost scenarios of another segment of the whole system. So they give 30 possible combinations all together (hope I counted it right ).

I have each of those 3 and 10 cost scenarios calculated separately and have results of them written in another sheet.

Now I need to make a summary sheet, where just results from chosen combination of two scenarios from scenario group 1 + 2 will be depicted. And all has to be defined by user's selection of the combination of those four parameter values in the "Scenario Control" sheet.

Problem is that it could be done by 30 IF functions in the formula , but the limitation of the formula length is against me... I wasn't able to find smarter way of doing it in order to bypass the limitation.

It's really urgent, I would be very grateful if somebody could help me!

Maybe by some VBA code...? I am not expert in this...

Thank you!  Register To Reply

2. ## Re: Formula Length Limitation

Perhaps you could use named formulas. Or simplify the formulas you have.  Register To Reply

3. ## Re: Formula Length Limitation

Since you are already calculating each permutation, it sounds like it's just a matter of selecting the one you need. You can do this using vlookup, Index/Match, or even sumproduct. If you will upload the file, it should be an easy fix.

SAE  Register To Reply

4. ## Re: Formula Length Limitation

Hi

Thank you for your interest! I apologize that I haven't replied sooner. I managed to solve the problem by splitting the formula into several steps and then just putting it together...

Thanks once again!  Register To Reply