R
RichardO
Hello all:
I have a couple of reports (5 in all) I run every morning. Everyday,
need to reformat each of the reports before it is submitted and th
procedure for formatting one of them is below.
I am looking for a way I could create a macro to automate th
procedures below so I can save some time. Can anyone please help out?
Thanks a lot. See procedure below.
Richardo
Steps are as follows:
Delete the first row with “Report”
Delete the Last column with title: Desc
Format the 2nd column & 4th column (titled Code and Invoic
respectively) to text
The Incurred column is looked up from another worksheet with th
following formula:
=IF(ISNA(VLOOKUP(B2,Incurred,3,0)),"NO",(VLOOKUP(B2,incurred,3,0)))
The Days column has the following formula:
=networkdays(E2,F2,holidays)
The last 3 columns have the following formula:
Past: if days column (column K) is less than 0 i.e. if K<=0,”YES”,”NO”
Track: if days column (column K) is > 5 i.e. if K>5,”YES”,”NO”
Now: if Past and Track columns are same, yes. I.e. i
L2=N2,”YES”,”NO”
Sort data in the following sequence in descending order
Current
Track
Now
Past
Sort data according to Analyst in Ascending order.
Where the analyst name changes, add 5 rows
Within an analyst group say Amanda, where Either Past or Now or Trac
changes, insert 2 rows.
In the rows created, put in “Past” and # of Past, Bold Cell
And/Or “Now” and # of Now, Bold Cell
And/Or “Track” and # of Track, Bold Cell
Then for the Analyst group type in Analyst name e.g. “Amanda” and Tota
# of Past, Now and Track, Bold Cell
Shade row one in grey
I have a couple of reports (5 in all) I run every morning. Everyday,
need to reformat each of the reports before it is submitted and th
procedure for formatting one of them is below.
I am looking for a way I could create a macro to automate th
procedures below so I can save some time. Can anyone please help out?
Thanks a lot. See procedure below.
Richardo
Steps are as follows:
Delete the first row with “Report”
Delete the Last column with title: Desc
Format the 2nd column & 4th column (titled Code and Invoic
respectively) to text
The Incurred column is looked up from another worksheet with th
following formula:
=IF(ISNA(VLOOKUP(B2,Incurred,3,0)),"NO",(VLOOKUP(B2,incurred,3,0)))
The Days column has the following formula:
=networkdays(E2,F2,holidays)
The last 3 columns have the following formula:
Past: if days column (column K) is less than 0 i.e. if K<=0,”YES”,”NO”
Track: if days column (column K) is > 5 i.e. if K>5,”YES”,”NO”
Now: if Past and Track columns are same, yes. I.e. i
L2=N2,”YES”,”NO”
Sort data in the following sequence in descending order
Current
Track
Now
Past
Sort data according to Analyst in Ascending order.
Where the analyst name changes, add 5 rows
Within an analyst group say Amanda, where Either Past or Now or Trac
changes, insert 2 rows.
In the rows created, put in “Past” and # of Past, Bold Cell
And/Or “Now” and # of Now, Bold Cell
And/Or “Track” and # of Track, Bold Cell
Then for the Analyst group type in Analyst name e.g. “Amanda” and Tota
# of Past, Now and Track, Bold Cell
Shade row one in grey