Link to home
Start Free TrialLog in
Avatar of karnac2020
karnac2020Flag for United States of America

asked on

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
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Try this file
Copy-of-MyInvoice.xlsx
SOLUTION
Avatar of richlando
richlando
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of karnac2020

ASKER

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
its just showing what's selected for copy, clicking away from it or pressing escape should clear it
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
missed the column totals

Reg
timecard-sample.xlsx
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?
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
Scratch that I was wrong.
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.
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
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
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
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
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
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
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
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
Did you turn macros on?
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
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
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.
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
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.
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
Periodstart is the cell which contains the start of week date
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
See if activex controls can serve your purpose.
Copy-of-MyInvoice.xlsm
ssaqibh

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

Mark
Oh

What else does?
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
The last file should work minus the controls.
Hi ssaqibh,

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

Thanks
Mark
On windows I enter design mode for VBA and then I can select the controls.

Here it is.
MyInvoice.xlsm
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
ssaqibh,

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

Mark
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
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
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
right click on the heading and choose 'delete table column' instead
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ssaqibh, regmigrant, richlando,

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

Mark