Hey all,
My excel skills are quite basic (ie minimal knowledge of macros and pivot tables etc) but I'm trying to make a spreadsheet to do something a lot more efficiently.
I'm struggling at the moment, and need a few tips to keep me going so hopefully can get some advice from you guys who actually know what you're doing!
Anyway I'll try outline the current situation and what I'm trying to achieve and if I could get some pointers that would be awesome! Currently I don't want to upload my spreadsheet directly as its moderately confidential information, but if what I've said doesn't make sense I can try upload if with some figures removed or something.
The Situation
A excavation record where I want to keep track of whats been dug, scheduled, etc. There are about a 1000 holes to be done over a large area of multiple roads and a time span over most of the year.
Now what I'm wanting to do is record the progress done each month, and calculate the cost of the works that has been done that month as well as what the total cost of the project has been so far (ie total of all months). There's a bit of variation in costs due due what stage of drilling we are in, depth of hole, and the type of surface to be resealed and so on.
Now what I'm wanting to do is set up a spreadsheet which will calculate this, and then have a macro that I can run to create a new sheet for the next month which includes the progress from the past month, (so will have all written info down, monthly costs in the sheet would reset, and the past months costs would be added to the total cost in that sheet). Ideally I'd be able to enter in the progress in that current month as work goes ahead, and monthly costs would start up again from zero. Once this month is done I could run the macro again, creating a new sheet for the new month, with data stored from the most recent month showing up on it. The process would repeat again for the next several months until all construction is done.
What I've done so far
I have got the spreadsheet done for the first month with nice formatting, all relevant headings etc. I have it so the relatively complicated scenario to find the cost from the process is based on multiple if statements (eg. ' ( =IF(H7="Isaac",$H$6,0)+IF(N7="BF",$N$6,0)+IF(N7="TBF",0,0)+(IF(Q7="S",1,0)(IF(P7="A",$P$2,0)+IF(P7="C",$P$3,0)+IF(P7="CS",$P$4,0)))+IF(T7="Y",$T$6,0)+IF(U7="Y",$U$6,0)+(($W$6W7)IF(W7="",0,1)) -* 'ugly but will do for now!).
I have tested is out and all works well! So now I am on to the stage where I will put in a macro and try get it so it calculates the next month.
I haven't done macros before, I tried having a wee crack at it but had a few problems.
Need help with
-Getting it so a new sheet is created, right after the sheet that was currently being worked on. It would ideally work on data of the current sheet, not the initial sheet used when macro created. Ie I created macro for January, and it produced a sheet for February based on January. However when viewing February and making a sheet for March it just bases itself of the January one again not February.
Having it so some sort of prompt comes asking, asking the user to confirm that all data from the month has been entered in and is correct, and then have it so there is an input which automatically names the worksheet something rather than having to type it in yourself each time (can do it, but would look way nicer and is less confusing for people if I can have this happen).
-The worksheet I was transferring data from earlier had some blank cells. I have it so the current work sheet copies the cells from the previous one for things like status completed, completion date etc (as I want it on record in the most current one as to the stage of every thing in the project). Currently I have it referring back to the previous sheet by having something like ' ='January 2015'!Q167 ' and this works fine when data has been recorded, but has the problem of blank cells being transferred over as zero rather than remaining blank. I know there's probably some easy way to fix this but I can't work it out!
Anyway that's it for now, let me know if there is stuff I haven't made clear or any more info needed.
I'm only just starting up with excel, and am still learning all the features and what it can do! Hopefully once I work this out and do a few more things my skills will improve a lot though.
P.s. I created my macro by record mode, rather than typing as I don't know VBA language yet.
Thanks so much!
My excel skills are quite basic (ie minimal knowledge of macros and pivot tables etc) but I'm trying to make a spreadsheet to do something a lot more efficiently.
I'm struggling at the moment, and need a few tips to keep me going so hopefully can get some advice from you guys who actually know what you're doing!
Anyway I'll try outline the current situation and what I'm trying to achieve and if I could get some pointers that would be awesome! Currently I don't want to upload my spreadsheet directly as its moderately confidential information, but if what I've said doesn't make sense I can try upload if with some figures removed or something.
The Situation
A excavation record where I want to keep track of whats been dug, scheduled, etc. There are about a 1000 holes to be done over a large area of multiple roads and a time span over most of the year.
Now what I'm wanting to do is record the progress done each month, and calculate the cost of the works that has been done that month as well as what the total cost of the project has been so far (ie total of all months). There's a bit of variation in costs due due what stage of drilling we are in, depth of hole, and the type of surface to be resealed and so on.
Now what I'm wanting to do is set up a spreadsheet which will calculate this, and then have a macro that I can run to create a new sheet for the next month which includes the progress from the past month, (so will have all written info down, monthly costs in the sheet would reset, and the past months costs would be added to the total cost in that sheet). Ideally I'd be able to enter in the progress in that current month as work goes ahead, and monthly costs would start up again from zero. Once this month is done I could run the macro again, creating a new sheet for the new month, with data stored from the most recent month showing up on it. The process would repeat again for the next several months until all construction is done.
What I've done so far
I have got the spreadsheet done for the first month with nice formatting, all relevant headings etc. I have it so the relatively complicated scenario to find the cost from the process is based on multiple if statements (eg. ' ( =IF(H7="Isaac",$H$6,0)+IF(N7="BF",$N$6,0)+IF(N7="TBF",0,0)+(IF(Q7="S",1,0)(IF(P7="A",$P$2,0)+IF(P7="C",$P$3,0)+IF(P7="CS",$P$4,0)))+IF(T7="Y",$T$6,0)+IF(U7="Y",$U$6,0)+(($W$6W7)IF(W7="",0,1)) -* 'ugly but will do for now!).
I have tested is out and all works well! So now I am on to the stage where I will put in a macro and try get it so it calculates the next month.
I haven't done macros before, I tried having a wee crack at it but had a few problems.
Need help with
-Getting it so a new sheet is created, right after the sheet that was currently being worked on. It would ideally work on data of the current sheet, not the initial sheet used when macro created. Ie I created macro for January, and it produced a sheet for February based on January. However when viewing February and making a sheet for March it just bases itself of the January one again not February.
Having it so some sort of prompt comes asking, asking the user to confirm that all data from the month has been entered in and is correct, and then have it so there is an input which automatically names the worksheet something rather than having to type it in yourself each time (can do it, but would look way nicer and is less confusing for people if I can have this happen).
-The worksheet I was transferring data from earlier had some blank cells. I have it so the current work sheet copies the cells from the previous one for things like status completed, completion date etc (as I want it on record in the most current one as to the stage of every thing in the project). Currently I have it referring back to the previous sheet by having something like ' ='January 2015'!Q167 ' and this works fine when data has been recorded, but has the problem of blank cells being transferred over as zero rather than remaining blank. I know there's probably some easy way to fix this but I can't work it out!
Anyway that's it for now, let me know if there is stuff I haven't made clear or any more info needed.
I'm only just starting up with excel, and am still learning all the features and what it can do! Hopefully once I work this out and do a few more things my skills will improve a lot though.
P.s. I created my macro by record mode, rather than typing as I don't know VBA language yet.
Thanks so much!
