PDA

View Full Version : Advice creating a Spreadsheet or smilar



Daniel.Drache
13-05-2014, 08:36 PM
I've just graduated from my apprenticeship to a code based contract.
Since I have a retainer that I have to exceed, I'm wanting to create a spreadsheet to keep track of the jobs I'm doing.
I would like to use Google Docs because it will be easier to access & input since I use 3 computers - my current work Laptop is WinXP, My soon to arrive work Tablet will be Win8 & my Personal Laptop is Win8.

I would like to track date, Job ID, Payment Code & Amount for that code. Ideally with a weekly total.
I realise I'll need to enter the info manually, But since I have 50+ codes each with a different payment I was hoping I could type the code in & spreadsheet would automatically enter the payment amount.

Would I be able to do something like this with Microsoft Spreadsheet?
Or is there another program that would work?

If I waited until I received my Win8 tablet (Or used my personal one) would it make it easier?

A rough idea of what I'm thinking
5721

Thanks for any advise
Daniel

wainuitech
13-05-2014, 09:22 PM
Quite easy really. What you can do is for the Job ID and Code use a drop down menu. Filling out the values as required for each task.

Found a video that shows you how to do it, just change the values as required.

http://www.youtube.com/watch?v=ns6qdpRgcLs

To insert the date you can use keys, Ctrl + : in the cell you want.

Daniel.Drache
14-05-2014, 08:40 AM
Thanks,
Ill give the video a watch after work

Daniel.Drache
14-05-2014, 06:05 PM
Job ID's are different every time so i don't need to worry about them

Thanks for the video the drop down lists looks really handy.

Is there a way to link the Code drop down list & Payment?
IE if I select code 392 in column C row 3, $56 appears in Column D row 3 as it shows on the pic

wainuitech
15-05-2014, 09:03 AM
Job ID's are different every time so i don't need to worry about them

Thanks for the video the drop down lists looks really handy.

Is there a way to link the Code drop down list & Payment?
IE if I select code 392 in column C row 3, $56 appears in Column D row 3 as it shows on the pic You should be able to.

Personally I'm not to sh1t hot on Excel formulas, someone who has a lot more knowledge will be able to post it.

If not, I'll ask my son, hes actually good with Excel, I had one I was trying to do for over a day for my invoicing, he came along, said "really :illogical - move over" within a few seconds had it working. -- Smart $&%^^* :D

wainuitech
15-05-2014, 09:53 PM
Ok, no ones answered the problem above ---- I asked My son who loves excel. Told him what you wanted to do -- got the usual look of "seriously" :illogical

Opens a spread sheet, filled it out like the video, added in some madeup pricing and codes ( 10 lines ).

EG Code 392 =$100 765 = $10 123= $21 etc, thumped out a formula using VLOOK and did exactly what you wanted. Me --> :waughh: :waughh: :confused: Couldn't keep up.

So yes it can be done. :thumbs:

Trying to write it out and explain so it makes sense -- That's a different matter.

He said Theres a lot on the internet explaining vlook and things you can do.

Daniel.Drache
16-05-2014, 08:35 AM
Thanks, I'll have a big search around on the net

Webdevguy
16-05-2014, 12:14 PM
You should be able to.

Personally I'm not to sh1t hot on Excel formulas, someone who has a lot more knowledge will be able to post it.

If not, I'll ask my son, hes actually good with Excel, I had one I was trying to do for over a day for my invoicing, he came along, said "really :illogical - move over" within a few seconds had it working. -- Smart $&%^^* :D

Young people, they just know everything!

Webdevguy
16-05-2014, 12:17 PM
Ok, no ones answered the problem above ---- I asked My son who loves excel. Told him what you wanted to do -- got the usual look of "seriously" :illogical

Opens a spread sheet, filled it out like the video, added in some madeup pricing and codes ( 10 lines ).

EG Code 392 =$100 765 = $10 123= $21 etc, thumped out a formula using VLOOK and did exactly what you wanted. Me --> :waughh: :waughh: :confused: Couldn't keep up.

So yes it can be done. :thumbs:

Trying to write it out and explain so it makes sense -- That's a different matter.

He said Theres a lot on the internet explaining vlook and things you can do.

Does your son have an interest in doing any other scripting like JavaScript, VB, .Net, PHP etc?

dugimodo
16-05-2014, 12:53 PM
I was going to suggest lookup tables also.
I muddle my way through excel with the help function and can usually get it to do what I want. Turns out all my attempts at BASIC programming about 100 yrs ago wasn't completely wasted either as a lot of Excel formulas and functions are very similar to BASIC.

It's an incredibly flexible tool though and can do a lot more than I ever play with.

wainuitech
16-05-2014, 03:20 PM
Does your son have an interest in doing any other scripting like JavaScript, VB, .Net, PHP etc? I know he does do some of that, but to be honest I dont know what. Hes very much to himself here at home , doesn't go announcing what hes doing unless we ask. Just knuckles down and does what He wants/has to do for his studies. Whys That ?

He did say last night when I asked, about the amount of entries that were mentioned, got the usual "sigh"- er ho hum, take about 15-20 minutes or so to get something working, longest time would be entering the data, as he put it, its nothing but data entry then do the formulas.

Webdevguy
16-05-2014, 03:30 PM
I know he does do some of that, but to be honest I dont know what. Hes very much to himself here at home , doesn't go announcing what hes doing unless we ask. Just knuckles down and does what He wants/has to do for his studies. Whys That ?

He did say last night when I asked, about the amount of entries that were mentioned, got the usual "sigh"- er ho hum, take about 15-20 minutes or so to get something working, longest time would be entering the data, as he put it, its nothing but data entry then do the formulas.

Well I figured that if he understands the logic of functions in Excel and VBS "easy" then it would be a fairly easy step to other client side languages like JavaScript and server side scripting. Also if he was going to get into developing games he would have a pretty awesome carrier potential ahead of him.

wainuitech
16-05-2014, 04:08 PM
Well I figured that if he understands the logic of functions in Excel and VBS "easy" then it would be a fairly easy step to other client side languages like JavaScript and server side scripting. Also if he was going to get into developing games he would have a pretty awesome carrier potential ahead of him. Good point, I know hes always mucking about when doing online gaming doing something relating to coding on the laptop. Desktop PC for gaming while the laptop sitting on the desk doing something else. You hardly know hes home half the time apart from the occasional outburst of *!!@&%^%) and laughter over the gaming chat coming from his room :nerd: When you ask what hes doing the usual reply is "just this" or "stuff' :illogical Ask him about excel and its as if hes a natural, mind you hes damn good at maths and figuring things out, does't take him long if theres something he cant do.

Webdevguy
16-05-2014, 05:33 PM
Good point, I know hes always mucking about when doing online gaming doing something relating to coding on the laptop. Desktop PC for gaming while the laptop sitting on the desk doing something else. You hardly know hes home half the time apart from the occasional outburst of *!!@&%^%) and laughter over the gaming chat coming from his room :nerd: When you ask what hes doing the usual reply is "just this" or "stuff' :illogical Ask him about excel and its as if hes a natural, mind you hes damn good at maths and figuring things out, does't take him long if theres something he cant do.
Well if he is interested in pursuing a career in game development - game design etc, media design school have a degree course in it.