Solved

Excel - Copy/paste cell with a formula

Posted on 2016-07-22
11
65 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
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 68

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 92

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
 
LVL 92

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 92

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 28

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 28

Expert Comment

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Freeze Panes Solution 6 28
Excel macro runs twice 13 51
how to re-acticvate Office 2010 6 25
the code is not looping through 11 34
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now