I am using an excel array function to select a range and apply formula based on reference cells. But, everytime i add or remove a values, i am able to copy paste the references using macro but the array formula is not updating the range to reselect the new range.
Here is my main table on top and bottom table what i want sing an array formula which i already achieved but when updating using vba its not taking new entries added/deleted by updating the range in the formula.
No. Name V1 V3 V3 V4
1 Wood 10 10 10 10
2 wood 28 28 28 28
3 tree 30 45 60 68
4 plastic 50 50 50 50
5 tree 50 50 50 50
6 iron 64 75 75 80
No. Name V1 V3 V3 V4
1 Wood - A 25 25 25 25
2 Wood - A 50 50 50 50
3 tree - A 50 50 75 75
4 plastic - A 75 75 75 75
5 tree - A 75 75 75 75
6 iron - A 75 100 100 100
First formula: Name column
=concatenate(A1:A6," - A")
- Ctrl+shift+enter - is giving me what i need in right table names column.
Second formula: values change
=value(if(C1:F6<25,"25",if(C1:F6<50,"50",if(C1:F6<75,"75","100"))))
This formula i used to assign actual values and the values in left table is forecast values. I can achieve this even using array "ctrl+shift+enter".
Problem: But the problem, is everytime i update a sheet by adding new entries like A7,A8,A9 while applying the formula using vba it's not taking the new range as A1:A9(A1:A6) for first formula and C1:F9(C1:C6) for second formula but taking the old ranges in brackets. Because, of which i am getting errors like #N/A as its not taking new range, so formula couldn't understand what is in the remaining cells.