Avatar of coreybryant
coreybryantFlag for United States of America asked on

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)
=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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
coreybryant

8/22/2022 - Mon
byundt

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)
gowflow

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
ASKER
coreybryant

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
coreybryant

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.
ASKER
coreybryant

Once again - thanks for the example, feedback, and quick/easy instructions!