Using 1 of 100 listed formules based on cell value

Thequestion

New Member
Joined
Jan 27, 2016
Messages
18
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 26, 2011
Messages
479
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.
 
Last edited:

Thequestion

New Member
Joined
Jan 27, 2016
Messages
18
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


trunten; [/QUOTE said:
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.
trunten; [/QUOTE said:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,935
Messages
5,598,945
Members
414,268
Latest member
bluebandersnatch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top