Solved

Adding rows to a formatted Excel Spreadsheet Template

Posted on 2013-12-13
45
313 Views
Last Modified: 2013-12-18
I want to use the attached Excel template for time tracking. However I would like to add multiple rows for each day of the week - for example 3 rows for Monday, etc. so that I can list more than one project on a particular day.

I am new to Excel and the rows seem like they have functions in them so I don't know a good way to duplicate rows so that they preserve the calculations.

For example, on Tuesday, I would like to be able to show work on 3 different projects. Can I do that with the way the spreadsheet is set up now, or do I need to add rows?

Any help would be appreciated. Thanks.

Mark
MyInvoice.xlsx
0
Comment
Question by:karnac2020
  • 21
  • 12
  • 8
  • +1
45 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39716888
Try this file
Copy-of-MyInvoice.xlsx
0
 

Assisted Solution

by:richlando
richlando earned 50 total points
ID: 39716904
Sure just right click inside the table on the row below where you want to insert and select insert > row from above. Then select a whole row and copy and paste to the new row you just inserted, you have to select the whole row both times. Repeat for additional rows.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39716948
COPY/Past method won't work because its a table and its been built poorly because the usual 'insert table rows' breaks the formulas. The hand cranked version produced by ssaqibh is probably the easiest way forward if this template is the one you have to use but its limited if you need more than three projects on a day and will look odd if you only work on 1 or 2.

you could use the 'monthly' sheet as a base and put your own date in multiple times for each day you work on a project (overwriting the formula) and delete any extra rows at the end,

Reg
0
 

Author Comment

by:karnac2020
ID: 39716950
Thanks,

When I do that, the row that I copied has a box of running ants around it that doesn't seem to go away. What is that about? and can I get rid of it?

Mark
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39716958
its just showing what's selected for copy, clicking away from it or pressing escape should clear it
0
 

Author Comment

by:karnac2020
ID: 39717038
Thanks, Escape did the trick.

I like that template - it just seems logical that you would have more then one project on any particular day. Do you see anything in that template that would allow you to input work on more than one project on a particular day? Or does it look like you just have to manually add more rows as you showed me?

There is an Excel 2003 template that I really like called "Timecard" but can't find it anywhere to download it. it looks like this the attached image. Is that hard to recreate? Does it use VBA?

Thanks for your help,

Mark
ZA001113318.GIF
0
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 150 total points
ID: 39717089
no - very simple. If you like the attached I can add some validation and date information

To add lines to this right click on a row and 'insert table rows'.

Reg


PS: -  Ssaqhib posted the original answer - please remember that when/if awarding points
timecard-sample.xlsx
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39717127
missed the column totals

Reg
timecard-sample.xlsx
0
 

Author Comment

by:karnac2020
ID: 39717293
Ssaqhib- Thanks for the comments. I like your table. If I want to add even more rows for a particular day, how do I do that?

Reg - You have been very helpful. Yes I like your attachment. I don't need the column called "Account Code." I would like it if you could add validation and date information. By the way, is there a way when it calls for a date, to have a calendar pop up instead of manually inputing a date?
0
 

Expert Comment

by:richlando
ID: 39717416
It doesn't break if you select the whole row above and drag the formatting down. Just select the row above and move cursor to the far lower right of the selection and when the cursor turns to a plus drag it to the row below.
Copy-of-MyInvoice.xlsx
0
 

Expert Comment

by:richlando
ID: 39717421
Scratch that I was wrong.
0
 

Expert Comment

by:richlando
ID: 39717437
Looking again I am not wrong, sure the date formula is copied and not incremented but doesn't need to be if your copying the same day of the week. My original post works or you can drag the formatting either way works.
0
 

Author Comment

by:karnac2020
ID: 39718108
Reg,

Not trying to rush you but wanted to make sure I didn't miss your posting. Were you going to send something with date information and validation? Is there a way to use a date picker to set the date instead of hand writing it in?

Thanks
Mark
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39721084
Sorry - weekend got in the way

What date information do you want to see? A start date at the top is obvious but would you want the dates in the columns instead of / as well as the Day?

I can protect the totals but other than that the validation from the original copy used a customer list, do you want that too?

Reg
0
 

Author Comment

by:karnac2020
ID: 39721582
Hi Reg,

Thanks for getting  back to me. I hate to change my mind like this, but upon further reflection, I prefer the format used in my original attachment, but as modified by ssaqibh in his comment to me. If you could make it so that a date choose replaced the dropdown box for Start Date, remove the column for Non-billable hours, and provide some mechanism for adding/deleting/editing rows (maybe by a dropdown box that had "insert new row" and "delete a row"), that would be great. I don't need to validate the customer list or the account lookups.

Thanks so much for your help!

Mar
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39721604
Ok, I'll have a look and post a revision, but it will have to be this evening (GMT)

- but: a date picker for the start date would require ActiveX control or other element which might not work across various excel versions. I'll see if I can find a generic option (that looks reasonable) and incorporate it but I can put validation in that ensures (for example) you can only enter a Monday - would that suffice?

Adding and deleting rows is straightforward - what do you mean by 'editing' ?

Reg
0
 

Author Comment

by:karnac2020
ID: 39721655
Hi Reg,

1. I am using a Mac and so am using Excel 2011 for the Mac. Maybe there is a date picker that would work with that?

2. I'm not sure what you meant with the validation that would ensure entering only Monday. I would like to have multiple rows for Monday, Tuesday, etc., and be able to add or delete rows for Monday, Tuesday, etc. depending on how many tasks i had on that day.

3. I guess I don't need "editing."

Tonight is fine (GMT). Thanks again.

Mark
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39721669
I'll do what I can but don't have access to a Mac so will have to guess.

I meant I could validate that any date entered as a 'start' was a Monday and work forward from there

Reg
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39721683
Does this help?
MyInvoice-1.xlsm
0
 

Author Comment

by:karnac2020
ID: 39721936
Reg,

No need to validate the Monday start date. I may want to pick another start date, so let's just leave that so it could be any day of the week.

Thanks
Mark
0
 

Author Comment

by:karnac2020
ID: 39721941
ssagibh,

Thanks for you comment. I looked at your attached file, but I didn't find that it had a date picker there. I am using Excel 2011 on a Mac.

Mark
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39722003
Did you turn macros on?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:karnac2020
ID: 39722032
ssagibh,

Now I see what you ate talking about. I didn't notice it before because my idea of a date picker was a calendar popup that lets you pick any date. The way you have it now, it looks like you have to pick from 8 days in 2016. I would like it popup a calendar where you could pick any day in any year as the start date. Would this be achievable?

Thanks
Mark
0
 

Author Comment

by:karnac2020
ID: 39722036
ssagibh,

Could this be added to the monthly time sheet as well?


I would like to be able to do both a weekly and a monthly time sheet, where the monthly one would add up the times from the weeks.

Mark
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39722063
I have had problems on different versions of excel on windows. I have absolutely no idea how to do this on the Mac.

Presently the validation list looks at 2 weeks before the date entered and 5 weeks after that date. So in a sequential work this should not be a problem.

If that cell is blank then it uses today's date as a reference.
0
 

Author Comment

by:karnac2020
ID: 39722076
ssqqibh,

The drop down box for choosing the date works fine on the Mac. However, the dates you have there seem to be 2016. Where do I change this, and could it be a calendar to pick the dates from any day?


Thanks
Mark
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39722098
This macro uses the date already entered in "Periodstart" as a reference. You can delete it to use today's date as a reference.

I now see a potential problem in this method wherein you would have problem in selecting a date far from now. You might have to go step by step.
0
 

Author Comment

by:karnac2020
ID: 39722123
ssaqibh,

Where do I find "Periodstart" and how do I change or delete it?

What did you mean by "You might have to go step by step"?

Thanks
Mark
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39722150
Periodstart is the cell which contains the start of week date
0
 

Author Comment

by:karnac2020
ID: 39722191
ssaqibh,

I deleted the contents of that cell so it starts out blank.

However, when I enter 12/9/2013 into the Start date, I get an error message that says "The value you entered is not in the required list." I am then taken to a page where the dates are blank like in the attached screenshot.

And the 2016 dates are still showing up. Where are those input?

Thanks for your help!

Mark
Screen-Shot-2013-12-16-at-1.58.4.jpg
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39722268
See if activex controls can serve your purpose.
Copy-of-MyInvoice.xlsm
0
 

Author Comment

by:karnac2020
ID: 39722282
ssaqibh

Thanks for the suggestion but actives controls don't work on the Mac.

Mark
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39722297
Oh

What else does?
0
 

Author Comment

by:karnac2020
ID: 39722609
Ssaqibh,

I don't know. I could live without the calendar pop up if I could get rid of the 2016 dates and put in my own.

Mark
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39723047
The last file should work minus the controls.
0
 

Author Comment

by:karnac2020
ID: 39723108
Hi ssaqibh,

Excuse me for my ignorance of Excel: how do I remove the controls?

Thanks
Mark
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39723158
On windows I enter design mode for VBA and then I can select the controls.

Here it is.
MyInvoice.xlsm
0
 

Author Comment

by:karnac2020
ID: 39725320
ssqqibh,

Thanks again for your response. I think it is almost exactly what I need now - just a few housekeeping details.

1. Can you remove the "Non-Billable Hours" column and still make the calculations in the other cells consistent. (I tried removing that column, but then got ! errors in calculated columns.)

2. When I click the dropdown arrow in front of Project (1 or 2) for any days, i get a yellow ! error icon to the left of the date column and don't know  how to fix it.

3. If you could make sure all the columns and rows have formulas that are consistent and don't produce errors, that would be great.


Thanks!

Mark
0
 

Author Comment

by:karnac2020
ID: 39725567
ssaqibh,

I also noticed that the dropdown box on the right of the projects column brings up the date instead of the projects.

Mark
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39725658
Try this file. I have removed almost all inconsistencies. There still is in date column but I think it should be ignored.

I had deleted the non-billable column without any errors. How do you delete the column. Selecting the entire column of the spreadsheet does not allow deleting. I selected a cell in the table, right-clicked and selected Delete > Table rows. This works just fine. Give it a try and then write back if you still have problems.
MyInvoice-2.xlsm
0
 

Author Comment

by:karnac2020
ID: 39725664
Why do you delete "table rows" when you just want to delete the column "Non-billable hours"? When I selected a cell in the Non-billable hours column, right clicked and selected Delete > Table rows, it deleted the whole row, which is not what I wanted. I kept doing it and it continually deletes rows until there is nothing left in the spreadsheet.

I must not be understanding you correctly. Sorry.

Mark
0
 

Author Comment

by:karnac2020
ID: 39725668
ssaqibh,

I also just noticed that when I put a new Week Start date in at the top, the Date column in the spreadsheet does NOT update. It did before though.

Mark
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39726276
right click on the heading and choose 'delete table column' instead
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 300 total points
ID: 39726338
karnac2020,

Why do you delete "table rows" when you just want to delete the column
Sorry, that was meant to be table columns.

when I put a new Week Start date in at the top, the Date column in the spreadsheet does NOT update
Previously I had been using a formula to do this so that the update was automatic. Subsequently you wanted to select the dates through a dropdown. Selecting the dropdown would replace the formula with a value. So I removed the formulas so that the two may not get mixed.

I am now uploading a file which updates the dates with the help of a macro so that there is no formula in there.
MyInvoice-2.xlsm
0
 

Author Comment

by:karnac2020
ID: 39726600
ssaqibh, regmigrant, richlando,

Thank you all for your help. This last version works well.

Mark
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now