karnac2020
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
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.
ASKER
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
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
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
ASKER
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
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
- 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
ASKER
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
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
I meant I could validate that any date entered as a 'start' was a Monday and work forward from there
Reg
Does this help?
MyInvoice-1.xlsm
MyInvoice-1.xlsm
ASKER
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
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
ASKER
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
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?
ASKER
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
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
ASKER
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
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.
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.
ASKER
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
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.
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.
ASKER
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
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
ASKER
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
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
Copy-of-MyInvoice.xlsm
ASKER
ssaqibh
Thanks for the suggestion but actives controls don't work on the Mac.
Mark
Thanks for the suggestion but actives controls don't work on the Mac.
Mark
Oh
What else does?
What else does?
ASKER
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
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.
ASKER
Hi ssaqibh,
Excuse me for my ignorance of Excel: how do I remove the controls?
Thanks
Mark
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
Here it is.
MyInvoice.xlsm
ASKER
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
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
ASKER
ssaqibh,
I also noticed that the dropdown box on the right of the projects column brings up the date instead of the projects.
Mark
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
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
ASKER
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
I must not be understanding you correctly. Sorry.
Mark
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ssaqibh, regmigrant, richlando,
Thank you all for your help. This last version works well.
Mark
Thank you all for your help. This last version works well.
Mark
Copy-of-MyInvoice.xlsx