Solved

Excel - Copy/paste cell with a formula

Posted on 2016-07-22
11
74 Views
Last Modified: 2016-07-22
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
0
Comment
Question by:Stephen Kairys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 70

Accepted Solution

by:
Qlemo earned 250 total points
ID: 41724595
"Paste special" is correct. It should have an option for "values", which is what you need.
Another way is to create a reference to the cell: In the target cell, click to edit, type an equal sign, then change to the source cell. Press return, and done.
1
 
LVL 95

Assisted Solution

by:John Hurst
John Hurst earned 125 total points
ID: 41724605
I agree with the above. In fact, I use the Customize Quick Access Toolbar to put Paste Value and Paste Formula icons right there. I use both all the time.
0
 
LVL 4

Author Comment

by:Stephen Kairys
ID: 41724621
OK, that worked...sort of. It seems that unless the cell is already formatted to be a time , it pastes a decimal value (e.g 0.20625) if I choose the first option. (Values).
Paste Special
Do I need to choose the 2nd option (Values and Number Formatting)?
0
Industry Leaders: 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!

 
LVL 95

Expert Comment

by:John Hurst
ID: 41724625
Paste values will paste decimals. No way around that.

Solution I use:   =ROUND("formula", 0) to round to integers. Then paste values.
0
 
LVL 4

Author Comment

by:Stephen Kairys
ID: 41724634
@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.
0
 
LVL 95

Expert Comment

by:John Hurst
ID: 41724637
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.
0
 
LVL 31

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 125 total points
ID: 41724658
Stephen,

Values and Number formatting option will retain the number formatting applied to the cell you copied, it will do the trick for you.
0
 
LVL 4

Author Closing Comment

by:Stephen Kairys
ID: 41724683
Great collaboration all! Thanks!
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41724686
You're welcome Stephen! Glad we could help.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41724724
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
0
 
LVL 4

Author Comment

by:Stephen Kairys
ID: 41724729
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!
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

695 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