Excel - Copy/paste cell with a formula
Excel 2016/Win10
Hi everyone. Happy Friday. :)
I maintain a timesheet for my work that contains daily totals. e.g. for 7/21/16, I might have worked on three things. The time for each project is in column C e.g.
Cell D100: 1:00
Cell D101: 0:30
Cell D102: 3:30
----------------------
Cell D103: =SUM(D100:D102) = 5:00
A second sheet contains a weekly calendar. I want to copy/paste (e.g) the 5:00 value in D103 (first sheet) into (e.g.) cell E22 (second sheet) which represents 7/21/16; however, Excel thinks I want to paste the formula (not the value).
I looked at paste special; however, it did not contain any options that seemed helpful.
Is there a workaround? (I'd rather avoid referencing the first sheet in the second sheet,)
Thanks,
Steve
Hi everyone. Happy Friday. :)
I maintain a timesheet for my work that contains daily totals. e.g. for 7/21/16, I might have worked on three things. The time for each project is in column C e.g.
Cell D100: 1:00
Cell D101: 0:30
Cell D102: 3:30
----------------------
Cell D103: =SUM(D100:D102) = 5:00
A second sheet contains a weekly calendar. I want to copy/paste (e.g) the 5:00 value in D103 (first sheet) into (e.g.) cell E22 (second sheet) which represents 7/21/16; however, Excel thinks I want to paste the formula (not the value).
I looked at paste special; however, it did not contain any options that seemed helpful.
Is there a workaround? (I'd rather avoid referencing the first sheet in the second sheet,)
Thanks,
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Paste values will paste decimals. No way around that.
Solution I use: =ROUND("formula", 0) to round to integers. Then paste values.
Solution I use: =ROUND("formula", 0) to round to integers. Then paste values.
ASKER
@John,
For the "ROUND" solution, is that in cell FROM which I'm copying?
Or, maybe "Values and Number formatting" ? That seems to work, but loses my boldfacing, which is no big deal.
Thanks.
For the "ROUND" solution, is that in cell FROM which I'm copying?
Or, maybe "Values and Number formatting" ? That seems to work, but loses my boldfacing, which is no big deal.
Thanks.
I prefer to make main formulas Round (as that is better from a calculation point of view in the work I do). Then I copy / paste values.
If your way is better for you, then you should use that.. Always more than one way in Excel.
If your way is better for you, then you should use that.. Always more than one way in Excel.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great collaboration all! Thanks!
You're welcome Stephen! Glad we could help.
I was about to post a solution when I refreshed and the question had been closed.
A couple of points:
@John - he is looking at time values so rounding to 0 decimal places will either round up to a whole day or round down to zero hours
@Stephen - When adding up time values you may sometimes go over 24 hours, especially with a weekly timesheet. Standard hh:mm formatting will only go up to 23:59; to get round this format as [h]:mm this will allow the hours portion to go over 24.
For example, if you have 5 days of 7:30, adding up comes to 37.5 hours, formatted as hh:mm will show 13:30 (the first 24 rounded back to a day). Formatted as [h]:mm shows 37:30.
Thanks
Rob H
A couple of points:
@John - he is looking at time values so rounding to 0 decimal places will either round up to a whole day or round down to zero hours
@Stephen - When adding up time values you may sometimes go over 24 hours, especially with a weekly timesheet. Standard hh:mm formatting will only go up to 23:59; to get round this format as [h]:mm this will allow the hours portion to go over 24.
For example, if you have 5 days of 7:30, adding up comes to 37.5 hours, formatted as hh:mm will show 13:30 (the first 24 rounded back to a day). Formatted as [h]:mm shows 37:30.
Thanks
Rob H
ASKER
Thanks, Rob. Fortunately, I sum only on work performed during a particular day. Unless I move to a planet whose days exceed 24 hours, I'm fine there. :) Thanks!
ASKER
Do I need to choose the 2nd option (Values and Number Formatting)?