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
[Uploaded an Excel file]
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
Short answer is No.
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.
[Uploaded an Excel file]
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.
[Uploaded an Excel file]
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
=SUMIF(Table2[ITEMCODE],Received!B3,Table2[QTY])
Excelchat Expert
10/08/2018 - 03:20
Table 2 being the "Received" sheet
Excelchat Expert
10/08/2018 - 03:21
Oh case sensitive, RECEIVED!B3 then
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.
