how to make a cell calculate a formula of an other cell

[moliky]

Hello,

so i have this issue with my table:

i want my cell on the "result" table to calcul a different formula according to 2 criterias in a "database" table. i manage to rechercheV my formula so it appears in the cell, but the calcul of the formula is not happening

table "result" :

criteria 1   -   criteria 2   -   My cell

table database

column A : combinaison of criteria 1&2= 32 differentes lines

column B : 32 lines corresponding to 32 differents formula to apply in my cell depending on criteria 1&2 combinaison

so my formula was:

RECHERCHEV(CONCATENER([@criteria1];[@criteria2]);Tableaudatabase;2;FAUX))

(sorry my formula is in french)

but with this formula, my formula appears as a text and isn't calculated

which fonction do i use to make so my formula is calculated?

thanks to helpers + sorry for bad english

[#2]

@moliky 

A formula should start with =

=RECHERCHEV(CONCATENER([@criteria1];[@criteria2]);Tableaudatabase;2;FAUX))

Does the first column of Tableaudatabase contain the combinations of the criteria without a space or other character in between?

Also make sure that the cell with the formula is not formatted as Text, but as (for example) General.

[#3]

@Hans Vogelaar 

my formula did start wtih the equal sign

=RECHERCHEV(CONCATENER([@criteria1];[@criteria2]);Tableaudatabase;2;FAUX))

and it worked, as the formula now appearing is the one i wanted from the database table. but it appears as text and isn't calculated

[#4]

@moliky 

Have you checked the remarks from my previous reply about the cell format and the Show Formulas button?

[#5]

yes, it's all rightly done

To put it simply, i need my cell A1 to calculate this text write in cell A2 :
SI([@[Résultat]]<=[@[Spec / limite sup]];"CF";"NC")
but this text is obtained by using the formula
=RECHERCHEV(CONCATENER([@criteria1];[@criteria2]);Tableaudatabase;2;FAUX))

[#6]

@moliky 

I am really sorry, but I can't visualize what you want.

[#7]

@Hans Vogelaari made an excel sheet to explain what i need.

i'm open to new suggestion too.

translation of the french text inside :

"Hello,
I want to calculate my compliance box (yellow) automatically according to the result (variable) and according to the limits set for each combination of reference/test.
The source of the values is in the tab "sources of the formulas" and are recalled with the help of RECHERCHEV in this table for practicality.
The formula to be used for the calculation of conformity is also recalled on this sheet from the sheet "sources of formulas" and is different for each line and each result.
A big part of the work is done, but I can't manage to switch from "the cell contains a formula in text form" to "the cell calculates the formula stored in text".
Maybe it's a common syntax error, but I'm stuck at this point.
Any ideas?

Thanks in advance."

[#8]

@moliky 

The attached version is a macro-enabled workbook; you will have to allow macros when you open it.

There is code in the worksheet module that will automatically change the Conformité formula if you change a cell in the Référence or Test columns.

[#9]

thank you for your answer,
but when i tried to use your code in my worksheet it didn't work.it is supposed to apply every time a "test" or "reference" cell is modified but if i erase the conformity cell then try to change the test it doesn't reappears.
and if i change the formula used to calculate the conformity it doesn't update in the conformity cell.
am i suppose to activate it manually?
Plus i was more looking for something else like a new function coded in VBA maybe, that would activate only when i use it

[#10]

@moliky 

If you clear a cell in the Conformité column, then change Référence or Test in the same row, the formula should reappear.

You write "if i change the formula used to calculate the conformity" - where do you change that formula?

[#11]

i don't understand why something simple like this won't work

Function EVAL(z As Range)
EVAL = "=" & z.Value
End Function

[#12]

it does not reappears
i changed it in the "sources des formules tab"

[#13]

@moliky 

Your function EVAL returns a fixed text string, not a formula.

[#14]

then how can i fix it so it returns a formula?

[#15]

@moliky 

(1) D2 contains a formula.

(2) I cleared D2.

(3) I changed B2, and as a result, D2 contains a formula again.

The formulas in column D will be updated when you change a value in columns B or C. To make then change when you edit a formula on the 'source des formules' sheet:

Right-click the sheet tab of the 'source des formules' sheet.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cl As Range
    If Not Intersect(Me.ListObjects("Tableau6").DataBodyRange, Target) Is Nothing Then
        For Each cl In Worksheets("résultats").ListObjects("Tableau2").ListColumns("Conformité").DataBodyRange
            cl.Formula = "=" & cl.Offset(0, 4).Value
        Next cl
    End If
End Sub

[#16]

@moliky 

---

@moliky wrote:
then how can i fix it so it returns a formula?

---

You'd have to  combine the rows of the 'source des formules' sheet into one ridiculously long formula.

[#17]

@moliky 

This would be the formula in D2 if you don't want to use the code. But as you can see, it would be a nightmare to maintain:

=IFS(A2="Produit fini", IFS(B2="test1", IF([@Résultat]<=[@[Spec / limite sup]],"CF","NC"), B2="test2", IF(AND([@Résultat]<=[@[Spec / limite sup]],[@Résultat]>=[@[Spec /limite inf]]),"CF","NC"), B2="test3", IF([@Résultat]<=[@Vigilance],"CF",IF(AND([@Résultat]>[@Vigilance],[@Résultat]<=[@[Spec / limite sup]]),"VIGILANCE","NC"))), A2="Semi fini", IFS(B2="test1", IF([@Résultat]<=[@[Spec / limite sup]],"CF","NC"), B2="test2", IF(AND([@Résultat]<=[@[Spec / limite sup]],[@Résultat]>=[@[Spec /limite inf]]),"CF","NC"), B2="test3", IF(AND([@Résultat]>=[@[Spec /limite inf]],[@Résultat]<=[@Vigilance]),"CF",IF(AND([@Résultat]>[@Vigilance],[@Résultat]<=[@[Spec / limite sup]]),"VIGILANCE","NC"))), A2="Mat.Première", IFS(B2="test1", IF(AND(ISBLANK([@[Spec /limite inf]]),ISBLANK([@Vigilance]),ISBLANK([@[Spec / limite sup]])),"N/A","OSEF"), B2="test2", IF([@Résultat]>=[@[Spec /limite inf]],"CF","NC"), B2="test3", IF([@Résultat]<=[@Vigilance],"OK","N/A")))

In French:

=SI.CONDITIONS(A2="Produit fini"; SI.CONDITIONS(B2="test1"; SI([@Résultat]<=[@[Spec / limite sup]];"CF";"NC"); B2="test2"; SI(ET([@Résultat]<=[@[Spec / limite sup]];[@Résultat]>=[@[Spec /limite inf]]);"CF";"NC"); B2="test3"; SI([@Résultat]<=[@Vigilance];"CF";SI(ET([@Résultat]>[@Vigilance];[@Résultat]<=[@[Spec / limite sup]]);"VIGILANCE";"NC"))); A2="Semi fini"; SI.CONDITIONS(B2="test1"; SI([@Résultat]<=[@[Spec / limite sup]];"CF";"NC"); B2="test2"; SI(ET([@Résultat]<=[@[Spec / limite sup]];[@Résultat]>=[@[Spec /limite inf]]);"CF";"NC"); B2="test3"; SI(ET([@Résultat]>=[@[Spec /limite inf]];[@Résultat]<=[@Vigilance]);"CF";SI(ET([@Résultat]>[@Vigilance];[@Résultat]<=[@[Spec / limite sup]]);"VIGILANCE";"NC"))); A2="Mat.Première"; SI.CONDITIONS(B2="test1"; SI(ET(ESTVIDE([@[Spec /limite inf]]);ESTVIDE([@Vigilance]);ESTVIDE([@[Spec / limite sup]]));"N/A";"OSEF"); B2="test2"; SI([@Résultat]>=[@[Spec /limite inf]];"CF";"NC"); B2="test3"; SI([@Résultat]<=[@Vigilance];"OK";"N/A")))

[#18]

@Hans Vogelaari send you back the files with yellow and blue colors to show the problem

[#19]

based on this suggestion, i had an idea:
instead of putting my formula as text, i associated it to a number (i've 4 differents formula in total) and used the IFS as follow :

=SI.CONDITIONS([@Résultat]="";"";;[@[N° formule conformité]]="1";SI([@Résultat]<=[@[Spec / limite sup]];"CONFORME";"NON CONFORME");[@[N° formule conformité]]="2";SI(ET([@[Signe du résultat]]="<";[@Résultat]<=[@[Spec / limite sup]]);"CONFORME";SI(ET([@[Signe du résultat]]="+";[@Résultat]<=[@[Spec / limite sup]]);"CONFORME";"NON CONFORME"));[@[N° formule conformité]]="3";SI(ET([@[Signe du résultat]]="<";[@Résultat]<=[@Vigilance]);"CONFORME";SI(ET([@[Signe du résultat]]="+";[@Résultat]<=[@Vigilance]);"CONFORME";SI(ET([@[Signe du résultat]]="+";[@Résultat]>[@Vigilance];[@Résultat]<=[@[Spec / limite sup]]);"VIGILANCE";"NON CONFORME")));[@[N° formule conformité]]="4";SI(ET([@Résultat]<=[@[Spec / limite sup]];[@Résultat]>=[@[Spec /limite inf]]);"CONFORME";"NON CONFORME"))

but it still doesn't work... have i made a mistake in the formula?

[#20]

@moliky 

I don't see blue colors. Here is the workbook with the formula.