**Question description:**

*This user has given permission to use the problem statement for this blog.*

i need help creating a formula to calculate total weight of orders in excel - each time I copy my formula to the end of another row- my root date moves along a column

Solved by A. S. in 28 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
13/09/2018 - 12:46

Hi, welcome to Got it Pro-Excel!

Excelchat Expert
13/09/2018 - 12:47

According to my diagnosis, you need help with creating a formula to calculate total weight of orders, is that correct?

Excelchat Expert
13/09/2018 - 12:47

Can you send your excel file through this chat?

User
13/09/2018 - 12:49

hi, yes I can send it - let me do that now

Excelchat Expert
13/09/2018 - 12:49

Thanks!

User
13/09/2018 - 12:50

[Uploaded an Excel file]

Excelchat Expert
13/09/2018 - 12:50

Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows. We also currently do not support VBA/Macro solutions.

User
13/09/2018 - 12:50

good thanks

Excelchat Expert
13/09/2018 - 12:51

Ok, I have you file opened from my end. Can you point me to the tab and cell where you want the formula?

User
13/09/2018 - 12:52

yes 1 minute, thewebsite has crashed my excel - i have to restart

Excelchat Expert
13/09/2018 - 12:52

Ok, sure.

User
13/09/2018 - 12:53

ok i cant open it in front of me, but at the bottom of each row I have calculations in there

Excelchat Expert
13/09/2018 - 12:54

Ok, I can see it.

Excelchat Expert
13/09/2018 - 12:54

What column is your weight?

User
13/09/2018 - 12:54

one is a pallet count, each figure placed in the columns that are blank calculate the amount divided by the pallet count in column H

User
13/09/2018 - 12:54

the big number (around 30000 to 50000) in the bottom of the rows

User
13/09/2018 - 12:55

is my weight, based on my figure, devided by pallet, then multiply by weight of each pallet (weight is in column I)

User
13/09/2018 - 12:55

now my first row calculations seem ok

User
13/09/2018 - 12:56

but when I try to copy those over, the calculation moves over - as in no longer calculated H and I - but I and J... how do I stop it doing that?

Excelchat Expert
13/09/2018 - 12:57

Ok, for that you'll need to change the cell reference of the formula to absolute. Let me explain.

Excelchat Expert
13/09/2018 - 12:58

Do you see cell references in a formula that has a dollar sign '$'

User
13/09/2018 - 12:58

ah... yep

Excelchat Expert
13/09/2018 - 12:58

example =$A$2

User
13/09/2018 - 12:59

ok so in changing the first row to all absolutes, then once I copy over to the other rows it keeps the same root columns to use for the calculation

Excelchat Expert
13/09/2018 - 12:59

Best way to understand it this is to do a simple hands-on

Excelchat Expert
13/09/2018 - 12:59

Can you see our shared sheet?

User
13/09/2018 - 01:00

nope

Excelchat Expert
13/09/2018 - 01:00

Even if you hit refresh?

Excelchat Expert
13/09/2018 - 01:00

Can you see it now?

Excelchat Expert
13/09/2018 - 01:01

That's great

Excelchat Expert
13/09/2018 - 01:01

Now if you take a look at cell B2.

Excelchat Expert
13/09/2018 - 01:02

of our Shared sheet.

Excelchat Expert
13/09/2018 - 01:02

Hi, are you still there?

User
13/09/2018 - 01:02

yes, im here

Excelchat Expert
13/09/2018 - 01:02

You'll see that it's a relative reference. Meaning no dollar signs in the reference.

User
13/09/2018 - 01:03

yep

Excelchat Expert
13/09/2018 - 01:03

Can you copy cell B2 and paste it to cell C2?

Excelchat Expert
13/09/2018 - 01:03

Now, look at cell C2.

Excelchat Expert
13/09/2018 - 01:04

You can see that the spreadsheet will adjust and will now reference cell B1.

Excelchat Expert
13/09/2018 - 01:04

Because we are using relative reference.

Excelchat Expert
13/09/2018 - 01:04

Try copying cell B2 to cell B3.

Excelchat Expert
13/09/2018 - 01:05

Great, check cell B3's formula.

Excelchat Expert
13/09/2018 - 01:05

What do you see?

User
13/09/2018 - 01:05

ok its using A2 as reference

Excelchat Expert
13/09/2018 - 01:05

Do you understand the concept of relative reference?

User
13/09/2018 - 01:05

yes, great thank you.

Excelchat Expert
13/09/2018 - 01:06

Now, let's try absolute reference.

Excelchat Expert
13/09/2018 - 01:06

In cell B2, I am now using an absolute reference.

Excelchat Expert
13/09/2018 - 01:06

You'll see dollar signs in the cell reference or the formula.

Excelchat Expert
13/09/2018 - 01:07

To put those dollar signs in, you can manually type it or highlight the formula in the formula bar then press F4.

Excelchat Expert
13/09/2018 - 01:07

Anyway, let's try that later. First, I'll explain how it works.

Excelchat Expert
13/09/2018 - 01:07

Try copying cell B2 to cell C2.

Excelchat Expert
13/09/2018 - 01:08

You'll see that the reference is still cell A1

Excelchat Expert
13/09/2018 - 01:08

Even if you copy that formula in to cell B3.

Excelchat Expert
13/09/2018 - 01:08

or anywhere it will always be referencing A1

Excelchat Expert
13/09/2018 - 01:09

Do you follow?

User
13/09/2018 - 01:09

yes

Excelchat Expert
13/09/2018 - 01:09

That should solve your question regarding the formula that is changing and adapting to different columns.

User
13/09/2018 - 01:10

thats great yes it does answer that question

Excelchat Expert
13/09/2018 - 01:10

That's great.

User
13/09/2018 - 01:10

I'll try adding that into my spreadsheet not - can't believe that it was so simple. :)

Excelchat Expert
13/09/2018 - 01:10

That's good to hear.

User
13/09/2018 - 01:10

now to find my F4 button... :)

Excelchat Expert
13/09/2018 - 01:10

You don't have F4 button on your keyboard?

User
13/09/2018 - 01:11

laptop and my FN with the key doesnt seem to work it

Excelchat Expert
13/09/2018 - 01:12

Hmm, I can't see your keyboard so I cannot assist you with that. For now, you can manually type the dollar sign.

Excelchat Expert
13/09/2018 - 01:12

Make sure that both the row and the column is absolute.

User
13/09/2018 - 01:12

thank you for the help.

Excelchat Expert
13/09/2018 - 01:12

Meaning =$A$1

Excelchat Expert
13/09/2018 - 01:12

Because there's also a relative row and relative column reference.

User
13/09/2018 - 01:12

ok perfect thanks again and have awonderful evening

Excelchat Expert
13/09/2018 - 01:12

Relative row is =$A1

Excelchat Expert
13/09/2018 - 01:13

Relative column is =A$1

Excelchat Expert
13/09/2018 - 01:13

That's great to hear.

Excelchat Expert
13/09/2018 - 01:13

Would there be anything else I can assist you with regards to the original question and the solution provided?

User
13/09/2018 - 01:13

no thank you

Excelchat Expert
13/09/2018 - 01:13

If that's all, I'd like to wish you have a very nice day ahead of you and we'd love to hear from you again. Thank you for using Got it Pro-Excel!

Excelchat Expert
13/09/2018 - 01:14

Feel free to end the session and leave a rating and comment if you liked our service. Warm regards!

User
13/09/2018 - 01:14

thank you and you too.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*