Using 1 of 100 listed formules based on cell value

[Thequestion]

Dear All,

I'm using a list with 100 different codes. I need a different extensive formula for each code.
Every month several new codes will be added to the list.

Is it possible to make a formula list based on certain cell values?

For example:

Code column with these numbers:
5
7
13
2

If cell value is 5 then use formula sumifs('NAV dump'!F:F;'NAV dump'!P:P;LINKS(O63;6);'NAV dump'!D:D;"800120")
If cell value is 7 then use formula vlookup (B6;K:L;2;0)

Combining more than 100 different formulas in 1 cell with nested ifs isn't an option.
It would be very useful if I can connect codes with a list of formulas.

Hopefully my description is clear.
Any help is greatly appreciated

 

[tyija1995]

This one sounds quite tricky indeed! Especially as new ones get added each month...

I'd try make a table with all of the codes and their respective formulae - then setup a dropdown list requesting a code number and have an INDEX MATCH set up to that chosen code number.

I really can't see a quicker/efficient way to do this though if there really are 100+ different codes & formulae associated to said codes.

 

[trunten]

Do these formulas change at all? By that I mean, are you planning to have the formulas use a value in that row or do they always return the same value? If it's the latter you could have your formula table set up then just to a lookup to pull through the result.

If it's the former then I don't know of anyway you could do that without a very cumbersome workaround using EVALUATE in a named range (would be willing to try though)

A macro could do it no problem though.

 

[Thequestion]

This one sounds quite tricky indeed! Especially as new ones get added each month...

I'd try make a table with all of the codes and their respective formulae - then setup a dropdown list requesting a code number and have an INDEX MATCH set up to that chosen code number.

I really can't see a quicker/efficient way to do this though if there really are 100+ different codes & formulae associated to said codes.

Thank you for your reply. Sorry for my late response. It was very busy during our monthly financial closing due to the absence of 2 colleagues.

I was already using a table but without a dropdown list because I'll be the only one working with this file and I need to copy paste all the codes each month.
A dropdown list always disappears after pasting on those specific cells right?

There are some checks in the file like Activa = Passiva of the Balance part. If a formula lookup doesn't work due to e.g. a space then I'll notice it.
I assume that you suggested a dropdown list to prevent errors

Do these formulas change at all? By that I mean, are you planning to have the formulas use a value in that row or do they always return the same value? If it's the latter you could have your formula table set up then just to a lookup to pull through the result.

If it's the former then I don't know of anyway you could do that without a very cumbersome workaround using EVALUATE in a named range (would be willing to try though)

A macro could do it no problem though.

Thank you for your reply. Sorry for my late response. It was very busy during our monthly financial closing due to the absence of 2 colleagues.
It's the former. I haven't used EVALUATE before, thank you for that suggestion, I'll look into it.
Thank you for offering your help but I don't want to waste your time on a cumbersome workaround

Unfortunately, I only have basic macro knowledge, I can only make simple macros and edit short understandable macros​