[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel - Copy/paste cell with a formula

Posted on 2016-07-22
11
Medium Priority
?
83 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 71

Accepted Solution

by:
Qlemo earned 1000 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 98

Assisted Solution

by:John Hurst
John Hurst earned 500 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 98

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 98

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 33

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 500 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 33

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

649 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