Solved

Excel - Copy/paste cell with a formula

Posted on 2016-07-22
11
62 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
Comment Utility
"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 90

Assisted Solution

by:John Hurst
John Hurst earned 125 total points
Comment Utility
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
Comment Utility
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 90

Expert Comment

by:John Hurst
Comment Utility
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
Comment Utility
@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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 90

Expert Comment

by:John Hurst
Comment Utility
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
Comment Utility
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
Comment Utility
Great collaboration all! Thanks!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome Stephen! Glad we could help.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

762 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

11 Experts available now in Live!

Get 1:1 Help Now