ok.
I have 3 columns like this
YV VA NUM
1 0 2.3
3 0 4.3
99 0 4.5
101 0 3.4
-23 0 4.2
0 1 42
0 -2 46
0 99 67
0 102 65
Now, I want to create some new additional columns based on these one. For instance could have
YV VA NUM (VA>100,NUM) (YV<0,NUM)
1 0 2.3 0 0
3 0 4.3 0 0
99 0 4.5 0 0
101 0 3.4 0 0
-23 0 4.2 0 4.2
0 1 42 0 0
0 -2 46 0 0
0 99 67 0 0
0 102 65 65 0
These new 2 columns can be achieved with simple IF functions.
However, in practice, I would have to create more than 2 calculated columns, perhaps even 10 to 20 of them. This gets laborious. I want to get a macro that adds these columns , with the formulas automatically. Perhaps have some sort of input sheet where I tell the macro what formulas to use. But, I am not sure how to do this.
What I would like is for the macro to search across the column names until it reaches NUM. Then, insert how many columns of calculated cells I specified and then put the formulas in automatocally.
Eventually I want to throw all this into a pivot table by the way.
Hope this makes some sense,
RET79
I have 3 columns like this
YV VA NUM
1 0 2.3
3 0 4.3
99 0 4.5
101 0 3.4
-23 0 4.2
0 1 42
0 -2 46
0 99 67
0 102 65
Now, I want to create some new additional columns based on these one. For instance could have
YV VA NUM (VA>100,NUM) (YV<0,NUM)
1 0 2.3 0 0
3 0 4.3 0 0
99 0 4.5 0 0
101 0 3.4 0 0
-23 0 4.2 0 4.2
0 1 42 0 0
0 -2 46 0 0
0 99 67 0 0
0 102 65 65 0
These new 2 columns can be achieved with simple IF functions.
However, in practice, I would have to create more than 2 calculated columns, perhaps even 10 to 20 of them. This gets laborious. I want to get a macro that adds these columns , with the formulas automatically. Perhaps have some sort of input sheet where I tell the macro what formulas to use. But, I am not sure how to do this.
What I would like is for the macro to search across the column names until it reaches NUM. Then, insert how many columns of calculated cells I specified and then put the formulas in automatocally.
Eventually I want to throw all this into a pivot table by the way.
Hope this makes some sense,
RET79