Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need shorthand to reference the column in the row im working on. For instance instead of writing =SUMIF(Table2[ITEM CODE],B8,Table2[QTY]) I want to write something like =SUMIF(Table2[ITEM CODE],Column(B),Table2[QTY])
Solved by C. C. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 10/08/2018 - 03:02
Greetings! Thank you for choosing Got It Pro!
User 10/08/2018 - 03:03
hi!
Excelchat Expert 10/08/2018 - 03:03
=SUMIF(Table2[ITEM CODE],B8,Table2[QTY]) Instead of B8, you want the whole B column right?
User 10/08/2018 - 03:03
no so
User 10/08/2018 - 03:03
one second
User 10/08/2018 - 03:04
ok right now i am trying to make a list of sums
Excelchat Expert 10/08/2018 - 03:04
Sure. Would you share your sheet?
User 10/08/2018 - 03:04
yes
Excelchat Expert 10/08/2018 - 03:04
ok
User 10/08/2018 - 03:05
User 10/08/2018 - 03:05
on the in stock tab
Excelchat Expert 10/08/2018 - 03:06
Okay, I'm there.
User 10/08/2018 - 03:06
im currently going down the Received qty tab
Excelchat Expert 10/08/2018 - 03:06
Okay
User 10/08/2018 - 03:06
and im changing the criteria so it matches each individual cell on column B
User 10/08/2018 - 03:07
instead of writing B6, B7, etc going down the entire column
User 10/08/2018 - 03:07
id like to know if there is a shorthand that i can use thats something like
User 10/08/2018 - 03:07
Column (B)
Excelchat Expert 10/08/2018 - 03:07
Can you tell me what you want the formula to do?
Excelchat Expert 10/08/2018 - 03:07
I think it's better to just rebuild it from scratch.
User 10/08/2018 - 03:08
oh yeah im just breaking it apart to learn from it its a bit of a mess
User 10/08/2018 - 03:08
sorry i know im wording this badly but ill try again.
Excelchat Expert 10/08/2018 - 03:08
No worries.
User 10/08/2018 - 03:08
so take a look at my formula on G3
Excelchat Expert 10/08/2018 - 03:09
Yeap.
User 10/08/2018 - 03:09
at the moment it is referencing cell B3 on the same sheet
Excelchat Expert 10/08/2018 - 03:09
=SUMIF(Table2[ITEM CODE],B3,Table2[QTY])
Excelchat Expert 10/08/2018 - 03:09
Yes
User 10/08/2018 - 03:09
I want to know if instead of writing B3
User 10/08/2018 - 03:09
i can write
User 10/08/2018 - 03:09
Column-something
User 10/08/2018 - 03:09
and it will just select the cell that is in "Column B" of the current row
Excelchat Expert 10/08/2018 - 03:09
Oh.
Excelchat Expert 10/08/2018 - 03:09
Excelchat Expert 10/08/2018 - 03:10
But.
Excelchat Expert 10/08/2018 - 03:10
You just need to enter the formula once.
Excelchat Expert 10/08/2018 - 03:10
And copy it down, it will automatically move.
Excelchat Expert 10/08/2018 - 03:10
Like, Row 3 is referencing B3 right? Row 4 would be referencing B4 and so on.
User 10/08/2018 - 03:11
yes thats what im trying to do, but so far ive been having to manually paste each fomula and change the 3 to a 4
User 10/08/2018 - 03:11
and im looking for a faster way im not aware of
Excelchat Expert 10/08/2018 - 03:11
Oh, whyyy.
Excelchat Expert 10/08/2018 - 03:11
That shouldn't be the case.
Excelchat Expert 10/08/2018 - 03:11
I just copy pasted the formula in B3.
Excelchat Expert 10/08/2018 - 03:11
Down
User 10/08/2018 - 03:12
and it changed it to B4 for you?
Excelchat Expert 10/08/2018 - 03:12
Yes
User 10/08/2018 - 03:12
actually wait everything is already changed up to
Excelchat Expert 10/08/2018 - 03:12
It's working just fine.
User 10/08/2018 - 03:12
b18
User 10/08/2018 - 03:12
G18
Excelchat Expert 10/08/2018 - 03:12
I did all the way down.
Excelchat Expert 10/08/2018 - 03:12
It's updated til G23.
User 10/08/2018 - 03:12
oh
Excelchat Expert 10/08/2018 - 03:13
Check this.
User 10/08/2018 - 03:13
thats strange
User 10/08/2018 - 03:13
how are you pasting it exactly its not doing it form e
User 10/08/2018 - 03:13
fo me*
User 10/08/2018 - 03:13
for me**
Excelchat Expert 10/08/2018 - 03:14
Copy the cell, then paste it down
User 10/08/2018 - 03:14
oh
User 10/08/2018 - 03:14
ooh
User 10/08/2018 - 03:14
wow thats simple
Excelchat Expert 10/08/2018 - 03:14
Did it work for you?
User 10/08/2018 - 03:15
yeah
Excelchat Expert 10/08/2018 - 03:15
Great!
User 10/08/2018 - 03:15
wait so if you could expand on that
Excelchat Expert 10/08/2018 - 03:15
You might have probably copying the text earlier instead of the cell.
User 10/08/2018 - 03:15
yea
Excelchat Expert 10/08/2018 - 03:15
Okay so that's why.
User 10/08/2018 - 03:15
but how does it know to change the criteria when i paste it down
Excelchat Expert 10/08/2018 - 03:16
When you copy a cell and paste it, excel automatically adjusts it for you because it assumes that you will change the cell reference as well.
User 10/08/2018 - 03:16
is it part of the sum formula to know that
Excelchat Expert 10/08/2018 - 03:16
Of all formulas actually.
User 10/08/2018 - 03:16
like why didnt it change the first section of it
Excelchat Expert 10/08/2018 - 03:16
It only changes cells.
Excelchat Expert 10/08/2018 - 03:16
So in this case, starting with B3, if you copy it one cell down, excel will now it's a B4.
Excelchat Expert 10/08/2018 - 03:16
And so on.
User 10/08/2018 - 03:16
i see
Excelchat Expert 10/08/2018 - 03:16
If it's a B3, and you try to copy it to the right instead, excel will know it's a C3
Excelchat Expert 10/08/2018 - 03:16
And so on.
User 10/08/2018 - 03:16
so if i did for instance
User 10/08/2018 - 03:17
=SUMIF(B3, ITEM CODE ,Table2[QTY])
User 10/08/2018 - 03:17
vs =SUMIF(Table2[ITEM CODE],B3,Table2[QTY])
User 10/08/2018 - 03:17
the cell is now in the first part of the equation instead of the middle
User 10/08/2018 - 03:17
does it still know to change just the first part
Excelchat Expert 10/08/2018 - 03:17
It will still move when you paste it because it's a cell.
Excelchat Expert 10/08/2018 - 03:18
Yes.
User 10/08/2018 - 03:18
ah i see
User 10/08/2018 - 03:18
and last question can you reference a cell from another table?
User 10/08/2018 - 03:18
i couldnt quite get it
Excelchat Expert 10/08/2018 - 03:18
Yes, you can.
Excelchat Expert 10/08/2018 - 03:18
You can reference a cell from anywhere.
User 10/08/2018 - 03:18
so instead of writing B3 in this =SUMIF(Table2[ITEMCODE],B3,Table2[QTY])
User 10/08/2018 - 03:19
how would i write it to grab B3 from table 2
Excelchat Expert 10/08/2018 - 03:20
Depending on what sheet it is located, it should look like this.
Excelchat Expert 10/08/2018 - 03:20
Excelchat Expert 10/08/2018 - 03:20
Table 2 being the "Received" sheet
Excelchat Expert 10/08/2018 - 03:21
User 10/08/2018 - 03:21
you mean table 2 is named Received
Excelchat Expert 10/08/2018 - 03:21
In any case, we only have a few minutes left. I'll mark this now as answered.
User 10/08/2018 - 03:21
yup sure
Excelchat Expert 10/08/2018 - 03:21
Yes because table 2 is in the sheet named Received
User 10/08/2018 - 03:21
oh youre still referencing my sheet lol
User 10/08/2018 - 03:22
alright got it
User 10/08/2018 - 03:22
ty
Excelchat Expert 10/08/2018 - 03:22
Yes. :)
Excelchat Expert 10/08/2018 - 03:22
Cool. Happy to be of help to you today!
Excelchat Expert 10/08/2018 - 03:22
Looking forward to helping you again for more of your spreadsheet needs.
Excelchat Expert 10/08/2018 - 03:22
Please consider leaving a feedback and a comment once the session is up!
Excelchat Expert 10/08/2018 - 03:22
Cheers! Have a great day1

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.