Solved

Using a Different Worksheet for Numbers and Not Copying Some Cells

Posted on 2013-12-13
6
302 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

732 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