Change formula based on imput

[Dennis Depoorter]

I like to change a formula based on the input of a cell.

I have a list with 2 company's: COMPANY1 and COMPANY2

If I select COMPANY1, the formula has to retrieve data from tab COMPANY1

If I select COMPANY2, the formula has to retrieve data from tab COMPANY2

Now the formula is like where i decide based on IF's what tab it has to choose.

=IF($E4="COMPANY1";IF($C4*1=1;VLOOKUP($A$2;COMPANY1!$A$2:$J$21;F$3;0);0);IF($E4="COMPANY2";IF($C4*1=1;VLOOKUP($A$2;COMPANY2!$A$2:$J$21;F$3;0);0);""))

But it would be better if its like:

=IF($C4*1=1;VLOOKUP($A$2;E4!$A$2:$J$21;F$3;0))

where E4 is the reference to a cell with a list in. This because today we have 2 company's in that list but tomorrow maybe 15. And that would be a hell of an IF :)

If we can "change the formula" based on input, we only have to add a tab and add an option to the list.

Attached the example file where in OVERVIEW we select a product and a supplier (company).

Because tab COMPANY1 and COMPANY2 are identical in format, it would be perfect it we can "change" the formula based on the list.

Thanks!!!

[#2 · Accepted solution]

Attaching a modified file with a suggested variable formula in Row 1.  This same approach could be used for the rest.

[#3]

@Dennis Depoorter 

You may use INDIRECT function to refer to the correct sheet listed in column E.

e.g. try this...

In F4

=IF($E4="","",IF($C4,VLOOKUP($A$2,INDIRECT("'"&$E4&"'!$A$2:$J$21"),F$3,0),""))

and then copy it across and down.

[#4]

@Savia  THANKS!!! now I'll try to understand the formula

[#5]

@Subodh_Tiwari_sktneerTHANKS!!! this works as well!!!