Solved

Using a Different Worksheet for Numbers and Not Copying Some Cells

Posted on 2013-12-13
6
303 Views
Last Modified: 2013-12-15
In a workbook, I have Prices and CNP (plus more)

On CNP, I basically need the total of Prices Values: ($6.63)
=SUM((A16*D3)+(A16*E3))
(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):
=SUM((A17*D4)+(A17*E4))

-or-

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)
=SUM((A16*D2)+(B16*E3))

and D/E17 should
=SUM((A17*D3)+(B17*E3)

But if I copy it, I get:
=SUM((A17*D4)+(B17*E4))


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
0
Comment
Question by:coreybryant
[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
  • 3
  • 2
6 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39718237
If you don't want the row references to update as you copy down, prefix them with a $.
=SUM((A16*D$3)+(B16*E$3))

or you could use SUMPRODUCT:
=SUMPRODUCT(A16:B16,D$3:E$3)
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39718629
Well if you post a sample workbook with the data you are referencing it would be much easier to understand what you are after.
gowflow
0
 
LVL 29

Author Comment

by:coreybryant
ID: 39720063
Basically, most of the static numbers are location on the Example Worksheet:


Then on CPN DE4
(PricesD3*CNPA4)+(PricesE3*CNPB5)

And on CNP F2
PricesG3+PricesH3

On Wireless DE5
(PricesD7*(Wireless5*Wireless5)+(PricesE7+PricesE8)

Wireless F5
((WirelessG7*WirelessH7)

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!
transactions-examples.xlsx
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39720560
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:
=SUM((A4*Prices!$D$3)+(A4*Prices!$E$3))
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:
=Prices!$G$3
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).
transactions-examplesQ28318091.xlsx
0
 
LVL 29

Author Comment

by:coreybryant
ID: 39720579
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.
0
 
LVL 29

Author Closing Comment

by:coreybryant
ID: 39720581
Once again - thanks for the example, feedback, and quick/easy instructions!
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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