Worksheet question

[Syed Mohammad Ali Quadri]

Dear Chandoo
I very hopeful that you can resolve my problem; I have enclosed my working sheet to explain you my problem
There is Shape Code from 20 to 87 having different formulas in Column "W and Y"; In calculation space there is also Shape code Column "H" I want to calculate length of bars as per Shape Code in Column "I" as per formula given in Column Y of same shape Code
I hope you, understand my problem
Thanks and Regards

Syed Quadri

 

[Luke M]

You can do this...but it takes a few conditions

1. Adjust the fomrulas in col Y to use all proper mathmetical operators so XL can do a simple substitution
2. Renamed some variables, as XL can't tell different between upper and lower case
3. inputs in K:T need to have a 0 if not used, not blank

I inserted col J for illustration, it's not needed and can be deleted. It's there to better show what the formula in col I is doing. We do a lengthy substitution filling in all variables, and then use the Excel 4.0 Evaluate method to calculate this out. Because of that last part, need to have file as xlsm instead of xlsx.

 

[NARAYANK991]

Hi Syed ,

First , please do not start a new thread , when the subject matter has been discussed in an earlier thread.

Second , when someone has posted a solution , and you have posted that you will give feedback , please do so ; Faseeh had posted a solution to which you had responded that you would clarify , please do that.

http://chandoo.org/forum/threads/by-changing-shape-code-formula-should-change.21144/#post-127506

Lastly , see this file for an alternative method ; it is substantially the same as Luke's solution ; instead of using the SUBSTITUTE function , it uses named ranges. Please note that I have used the following for the original variables :

LCR ( Lower Case R ) <-----> r

UCC ( Upper Case C ) <-----> C

UCR ( Upper Case R ) <-----> R

Narayan