Hi
Hi there. I am currently working on a consulting timesheet which basically gets created from our billing system which lets you extract timesheet data data to Excel. The timesheet includes data relating to consulting time/date/hours worked/name of consultant/Narration of job worked on and other info for a particular task for different clients. So each time a consultant spends time on a client it is logged in the billing system. When it is extracted from the system, i get a long report sheet which shows all the time worked on during say the last month (date range can be changed to what you wish) by the 3 consultants we have, for all clients.
My question is to ask- how i can run a pivot/table (which seems very hard and like its not going to work??) or more likely a Macro which will allow me to extract all the items relating to 1 particular client and copy it into a new sheet.. so that new sheet shows data only relating to 1 client... I want to do this for all clients?? As this is a task i have to do on a monthly basis i would be grateful if you could help me automate it so i just have to click or run the macro and it selects all the data relating to the client i want and it creates/pastes it into a new sheet??
Also within the same sheet... When the data is extracted from the original system... It does not have all the data fields i require - so i need to add a few columns each time...The fields i need to add is "Day" which shows the equivalent day of the "Date" shown for each task. Also i need to add a "chargeable" column (simple Y/N column) to show whether we need to charge the client or not... and finally a "HH/MM" column which converts the "Consulting Time" field into proper time as the "Consulting Time" is shown in Units produced by the original system, not as proper time. I have worked out a formula for it to convert the time shown in units into proper std time: (=INT(G3)&" h"&IF(INT(G3)=1," "," ")&MINUTE(G3/24)&"m"&IF(HOUR(G3/24)=1,""," ")
So is there anyway to automate this process and add these few columns automatically from a Macro or something??
If you need any further information please do not hesitate to contact me.
Hi there. I am currently working on a consulting timesheet which basically gets created from our billing system which lets you extract timesheet data data to Excel. The timesheet includes data relating to consulting time/date/hours worked/name of consultant/Narration of job worked on and other info for a particular task for different clients. So each time a consultant spends time on a client it is logged in the billing system. When it is extracted from the system, i get a long report sheet which shows all the time worked on during say the last month (date range can be changed to what you wish) by the 3 consultants we have, for all clients.
My question is to ask- how i can run a pivot/table (which seems very hard and like its not going to work??) or more likely a Macro which will allow me to extract all the items relating to 1 particular client and copy it into a new sheet.. so that new sheet shows data only relating to 1 client... I want to do this for all clients?? As this is a task i have to do on a monthly basis i would be grateful if you could help me automate it so i just have to click or run the macro and it selects all the data relating to the client i want and it creates/pastes it into a new sheet??
Also within the same sheet... When the data is extracted from the original system... It does not have all the data fields i require - so i need to add a few columns each time...The fields i need to add is "Day" which shows the equivalent day of the "Date" shown for each task. Also i need to add a "chargeable" column (simple Y/N column) to show whether we need to charge the client or not... and finally a "HH/MM" column which converts the "Consulting Time" field into proper time as the "Consulting Time" is shown in Units produced by the original system, not as proper time. I have worked out a formula for it to convert the time shown in units into proper std time: (=INT(G3)&" h"&IF(INT(G3)=1," "," ")&MINUTE(G3/24)&"m"&IF(HOUR(G3/24)=1,""," ")
So is there anyway to automate this process and add these few columns automatically from a Macro or something??
If you need any further information please do not hesitate to contact me.