ilogic

[Anonymous]

Hi.

I have a model whose measurements depend on two variables.
I created two parameters to control them. These parameters are multiple and their values ​​come from a spreadsheet.
The problem is that a parameter varies depending on the first, and when the first parameter changes, the worksheet does not update and consequently the multiple values ​​of the second parameter also do not change.
Attached are the template and spreadsheet.

[clutsa]

Can you put this at the top of "Diametro"? It will force the "SCH" to be an option from excel (AKA if SCH40 isn't an option it will force it to the first option that is available) 

MultiValue.SetValueOptions(True, DefaultIndex := 0)

[Anonymous]

did not work... in the form the SCH parameter does not change when the TUBE parameter changes. For example, put 4in in TUBE and SCH will show SCH40, SCH10. If TUBE is 12in, SCH should switch to SCH20, SCH10S, following the worksheet.

[clutsa]

I didn't see you had a form... I was changing them directly under the parameters.

[clutsa]

Well I'm not saying that this can't be done but what is your reason for wanting to do the formula with a spreadsheet over having the code in the document? Would you be open to having the list of possible combos built in Inv and just the lookup values in the spreadsheet?

[Anonymous]

I would like the spreadsheet to control the template. I have an equipment that uses several combos because it is modeled with several variables. and excel would make it much easier, since I do not have much programming experience in VBA. However testing in the equipment model, the behavior of the parameter changes of the combos has improved significantly.

[clutsa · Accepted solution]

So i laid the data out differently so we aren't using any dynamic formulas in the excel sheet. I think this is a better way of coming at this...

Code for "DIAMETRO"

excelFile = "your_filepath_and_name_here"
MultiValue.SetValueOptions(True, DefaultIndex := 0)
MultiValue.List("TUBO") = GoExcel.CellValues(excelFile, "Planilha1 (2)", "A2", "A11")
i = GoExcel.FindRow(excelFile, "Planilha1 (2)","Size", "=", TUBO)
MultiValue.List("SCH") = GoExcel.CellValues("B" & i, "C" & i)
'MessageBox.Show(Parameter("TUBO") & Parameter("SCH"), "Title")
h = GoExcel.FindRow(excelFile, "Planilha1 (2)", "TUBO&SCH", "=", Parameter("TUBO") & Parameter("SCH"))
d_ext= GoExcel.CellValue("G" & h)
esp= GoExcel.CellValue("H" & h)

and attached (if I did it right) is the excel file with the data arranged differently...

[Anonymous]

It worked...

Congratulations!

Let's see how I will do with a more complex model ..

[clutsa]

Boa sorte!