[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Adding rows to a formatted Excel Spreadsheet Template

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
karnac2020
Asked:
karnac2020
  • 21
  • 12
  • 8
  • +1
3 Solutions
 
Saqib Husain, SyedEngineerCommented:
Try this file
Copy-of-MyInvoice.xlsx
0
 
richlandoCommented:
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
 
regmigrantCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
karnac2020Author Commented:
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
 
regmigrantCommented:
its just showing what's selected for copy, clicking away from it or pressing escape should clear it
0
 
karnac2020Author Commented:
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
 
regmigrantCommented:
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
 
regmigrantCommented:
missed the column totals

Reg
timecard-sample.xlsx
0
 
karnac2020Author Commented:
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
 
richlandoCommented:
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
 
richlandoCommented:
Scratch that I was wrong.
0
 
richlandoCommented:
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
 
karnac2020Author Commented:
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
 
regmigrantCommented:
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
 
karnac2020Author Commented:
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
 
regmigrantCommented:
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
 
karnac2020Author Commented:
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
 
regmigrantCommented:
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
 
Saqib Husain, SyedEngineerCommented:
Does this help?
MyInvoice-1.xlsm
0
 
karnac2020Author Commented:
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
 
karnac2020Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Did you turn macros on?
0
 
karnac2020Author Commented:
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
 
karnac2020Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
karnac2020Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
karnac2020Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Periodstart is the cell which contains the start of week date
0
 
karnac2020Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
See if activex controls can serve your purpose.
Copy-of-MyInvoice.xlsm
0
 
karnac2020Author Commented:
ssaqibh

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

Mark
0
 
Saqib Husain, SyedEngineerCommented:
Oh

What else does?
0
 
karnac2020Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
The last file should work minus the controls.
0
 
karnac2020Author Commented:
Hi ssaqibh,

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

Thanks
Mark
0
 
Saqib Husain, SyedEngineerCommented:
On windows I enter design mode for VBA and then I can select the controls.

Here it is.
MyInvoice.xlsm
0
 
karnac2020Author Commented:
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
 
karnac2020Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
karnac2020Author Commented:
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
 
karnac2020Author Commented:
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
 
regmigrantCommented:
right click on the heading and choose 'delete table column' instead
0
 
Saqib Husain, SyedEngineerCommented:
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
 
karnac2020Author Commented:
ssaqibh, regmigrant, richlando,

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

Mark
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 21
  • 12
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now