Input Automatic calculation

[misc1992]

In attached file, I need to find a way for "y" to be automatically calculated. But for that, "y" needs to first find out which equation to use, depending upon the corresponding entry in first column.

would somebody know how to do it ?
I was trying vlookup, but couldn't.

Many many thanks in anticipation of a solution.
Best regards

[mrice]

I would suggest that you set up a set of columns in which the possible values of Y are calculated for each formula. Then use the find function to work out which row has the relevant formula and use the row number to set the index number in a vlookup function to give back the correct column.

Hope this makes sense.

[shg]

Welcome to the forum.

See attached.

[martindwilson]

shg where does the evaluate function come from? i can only find it in eval in the morefunctions add in ,not evaluate.or is it my excel is so old?

[shg]

It's an old XL4 macro, Martin.

[misc1992]

Many many thanks shb. Great help. It works fantastically.

But I can’t see the macro EvalTest. How can I see it ?

In the Sheet1, y does not change if x is changed. y changes only if I copy “=IF(ISNA(frm), "", Eval)” again in y column. Can you make it such that y changes automatically when x changes.

I suppose it wouldn't work if there are some other columns inserted between columns A,B,C in the sheet1, right ?

Regards

[mrice]

Here's an example with a VLOOKUP function. Please see the attachment.

[shg]

Please Login or Register  to view this content.

It's not a macro per se; it's done with names and the Excel 4.0 Evaluate macro, which is more like a function. There's no code in the workbook.

There's a named formula frm that does a lookup of the formula on the second sheet based on the Spec in column A. Do Insert > Name > Define to see it.

Eval (which is defined as =EVALUATE(Sheet1!frm)) uses the Evaluate function to evaluate the formula returned by frm.

EvalTest is another use of the Evaluate function on the Specs sheet that is just used to verify that the expression is entered correctly; it evaluates the expression for x=1.

In the Sheet1, y does not change if x is changed. y changes only if I copy “=IF(ISNA(frm), "", Eval)” again in y column. Can you make it such that y changes automatically when x changes.

Hmm. Change the formula in C2 and down to

=IF(ISNA(frm), "", Eval + 0 * ISNUMBER(x))

That forces Excel to see a dependency, and does not change the result.

I suppose it wouldn't work if there are some other columns inserted between columns A,B,C in the sheet1, right ?

Doesn't bother it at all.

[misc1992]

Thanks a lot buddy. Great help. Best regards.

[shg]

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

[misc1992]

Dear shg (Moderator),
Edit option does not show any "Go Advanced" option anymore. Probably thats because more than 2 days have elapsed. Could you be kind enough to close it as SOLVED please ?Thanks.

Also, would be be knowing about the second post that I had made on "how to join several worksheets together"
Regards