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.
