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