Solved

Using a Different Worksheet for Numbers and Not Copying Some Cells

Posted on 2013-12-13
6
304 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 31

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

615 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