Dynamic calculation in VBA

[Taihenwarui]

Hi. I'm a very new to Excel and VBA
I have been given a task to improve and automate the complex engineering calculation spreadsheet.

The problem is to replace a hidden table values ( which are needed for a final calculation calculation) with on the fly , dynamic calculation ( more elegant way as by boss described it ) in VBA and macro.

The formula for the final cell is

=IF(AS11="yes",IF(NOT(ISERROR(SUM(AV11,AX11,AZ11,BB11,BD11,BF11,BH11,BJ11,BL11,BN11))),SUM(AV11,AX11,AZ11,BB11,BD11,BF11,BH11,BJ11,BL11,BN11),0),"")

where AV11,AX11,AZ11,BB11,BD11,BF11,BH11,BJ11,BL11,BN11 are the hidden columns made for a calculation of the final value.

Its getting more complicated as the number of hidden columns depends of another factor . Here is the formula for a hidden column

=IF(AW11>0,INDEX($AR$11:$AR$24,AW11),0)

The column value depends of the another factor . In this case it depends of the value of AW11 cell.
The formula for AW11

=IF(ISERROR(IF(MATCH(A11,$H$11:$H$24,0)>0,MATCH(A11,$H$11:$H$24,0),MATCH(A11,$J$11:$J$24,0))),0,IF(MATCH(A11,$H$11:$H$24,0)>0,MATCH(A11,$H$11:$H$24,0),MATCH(A11,$J$11:$J$24,0)))

And so on. So with the grow of the calculation the number of hidden column can be bigger. It can be one or five.

My questions is if anyone has ever encountered such a problem and if this is feasible to make such a calculation in VBA (trigger by the event) to replace getting values from a column by calculation using marco and vba?

Thank you

 

[oldbrewer]

It is sometimes better to knock the house down completely and rebuild it rather than keep on building new bits

I would be happy to assist with that, but regret I do not understand your needs. Words are fine to you because you are very familiar with the spreadsheet, we are not

Why are columns hidden, is it security or aesthetics or something else?