• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Using a Different Worksheet for Numbers and Not Copying Some Cells

In a workbook, I have Prices and CNP (plus more)

On CNP, I basically need the total of Prices Values: ($6.63)
(I did this on the same sheet).

And I need to copy this several times one sheet 2 (I am going to rename them - should I do this now).

But I also need to copy this.  When I copy this, it is ($7.25):


if that is not possible, (how) can I copy cells but keep certain cells the same (on the same worksheet)?

For example, D/E16 (merged)

and D/E17 should

But if I copy it, I get:

Is there a way for Excel to copy some of the data?  

I will have about ten work sheets in this workbook.

I could put everything on the same worksheet if necessary /easier, but I rather not.

Thank you
  • 3
  • 2
1 Solution
If you don't want the row references to update as you copy down, prefix them with a $.

or you could use SUMPRODUCT:
Well if you post a sample workbook with the data you are referencing it would be much easier to understand what you are after.
coreybryantAuthor Commented:
Basically, most of the static numbers are location on the Example Worksheet:

Then on CPN DE4

And on CNP F2

On Wireless DE5

Wireless F5

I think that is right.  Basically the Prices worksheet doesn't really change.  

For what I could not figure out, I just used .0209

In regards to the DE columns, I merge them together.  I had to merge them when they were on the same sheet

Thank you!
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I was able to figure out the formulas for CNP worksheet, but not the ones for Wireless worksheet.

For CNP worksheet cell D4, I suggest a formula like:
As you copy this formula down, the references to Prices worksheet cells D3 and E3 will not change because of the $ before the row numbers.

For CNP worksheet cell F4, I suggest a formula like:
Once again, you may copy this formula down, and the reference to row 3 won't change.

You can see these formulas in the attached workbook.

One easy way to build a formula like these is:
1. Create the formula using placeholder values for the constants from the Prices worksheet
2. Edit the formula by selecting the placeholder value, then clicking on the Prices worksheet in the cell that contains the desired value
3. Hit the F4 key to change the cell reference from relative (Prices!D3) to absolute (Prices!$D$3). The F4 key toggles the selected part of the formula between relative addressing, absolute addressing, relative row & absolute column, and absolute row & relative column. In your case, you just need to click the F4 key once to get the absolute addressing (doesn't change as you copy down or across).
coreybryantAuthor Commented:
Thank you so much!  I have another workbook that uses a couple of worksheets but it is "backwards"...  the third worksheet - I enter the prices, then it goes to the second for monthly and the first is the category, but I could not figure out how to get this switched.

And very odd.  There was a formula even when I had it on one worksheet in the Wireless that did not work on the same row and everything matched.

Thanks for the tip, this workbook helps me much easier to separate the prices.
coreybryantAuthor Commented:
Once again - thanks for the example, feedback, and quick/easy instructions!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now