make formula "dynamic"

[ndendrinos]

=IF(ISERROR(sumgrey($H$17:$H$35)*G37),"",sumgrey($H$17:$H$35)*G37)

Hello : Instead of range H17:H35 can the formula work as cells are filled within that same range?
The reason I need to do this is because the formula shown above does not work if there are blank cells within that range
If I fill the blank cells with zeros then the formula works but that interferes with further code so the unused cells must remain "blank"

Many thanks

 

[Krishnakumar]

Hi,

=sumgrey($H$17:INDEX($H$17:$H$35,MATCH(9.9999E+307,$H$17:$H$35)))*G37

HTH

 

[ndendrinos]

EXCELLENT Krishnakumar Thank you

 

[T. Valko]

=IF(ISERROR(sumgrey($H$17:$H$35)*G37),"",sumgrey($H$17:$H$35)*G37)

Hello : Instead of range H17:H35 can the formula work as cells are filled within that same range?
The reason I need to do this is because the formula shown above does not work if there are blank cells within that range
If I fill the blank cells with zeros then the formula works but that interferes with further code so the unused cells must remain "blank"

Many thanks

What type of data is entered in the cells?

sumgrey sounds like a UDF that sums the values of cells that have been formatted with a grey color.

Is the data entered as a contiguous block?

 

[ndendrinos]

hello T.Valco
Answer to your questions 2&3 is yes

As for the first question nothing is entered in range H17:H35
the formula in those cells is (example for G21)
=IF(OR(G21="",$G$36=""),"",((G21)*(-$G$36))-(-G21))

G36 is a discount percentage (i.e.5%)

G37&G38 contain the formula Krishnakumar supplied here
G37 is one type of tax G38 is another

Some items are taxable (the ones in "grey") while some are not (these are left "Automatic")