Hi I'm wishing to use spreadsheet formula within a macro to get a desired result. I understand the concept of using a formula with a macro but having it work a little for complex i'm totally stuck!
My spreadsheet looks as follows (each individual has a tab like this one)
A B C D E F G
1
2 M T W T F Week
3 StatA x x x x x x
4 StatB
5 %A
6 StatC
The above is replicated 52 times to represent the 52 weeks in the year. Each of the 52 weeks is allocated against a month, i.e. week 1, 2, 3 ,4 is January etc (if a week has days that fall in two different months its 'allocated' to the month that has the most days of that week). This is detailed on another tab.
January 1 2 3 4
February 5 6 7 8
March 9 10 11 12 13
I've then got a further tab detailing 'stats'
So the stats tab looks like
Jan Feb Mar Apr May
Name1
Name2
Name3
What I want it to do is look at the month tab, recognise the weeks that are allocated against a specific month and then using that find those weeks in the individual tabs and take StatB away from StatA (say giving StatXY) and then compare StatC against StatXY to give a %. Input that % into the relevant month (from the months tab) against the relevant person.
All this is achievable via a fair bit of manual formula, wondering whether this achievable via a macro? The logic and concept is there (if i've explained it right).
Thanks.
Mark.
My spreadsheet looks as follows (each individual has a tab like this one)
A B C D E F G
1
2 M T W T F Week
3 StatA x x x x x x
4 StatB
5 %A
6 StatC
The above is replicated 52 times to represent the 52 weeks in the year. Each of the 52 weeks is allocated against a month, i.e. week 1, 2, 3 ,4 is January etc (if a week has days that fall in two different months its 'allocated' to the month that has the most days of that week). This is detailed on another tab.
January 1 2 3 4
February 5 6 7 8
March 9 10 11 12 13
I've then got a further tab detailing 'stats'
So the stats tab looks like
Jan Feb Mar Apr May
Name1
Name2
Name3
What I want it to do is look at the month tab, recognise the weeks that are allocated against a specific month and then using that find those weeks in the individual tabs and take StatB away from StatA (say giving StatXY) and then compare StatC against StatXY to give a %. Input that % into the relevant month (from the months tab) against the relevant person.
All this is achievable via a fair bit of manual formula, wondering whether this achievable via a macro? The logic and concept is there (if i've explained it right).
Thanks.
Mark.
