# Excel - COLUMN Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.