Excel - COLUMN Function Problem - Expert Solution

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

I am trying to make a pulled inventory sheet for work. In column A i have a part number, in B i have the quantity of the part, and in C i have a place to put an "x" when the product is used. I want to link these in a different tab labeled pulled inventory so that every time i mark "x" for complete, the pulled inventory is populated with the part and quantity used.
Solved by A. B. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 18/06/2018 - 07:12
Hi…Welcome to Got It Pro
User 18/06/2018 - 07:12
hi
Excelchat Expert 18/06/2018 - 07:12
Could you please share your data so that its easier for us to start working on the formula/
User 18/06/2018 - 07:13
can i use the table to create a scenario
Excelchat Expert 18/06/2018 - 07:13
Sure
User 18/06/2018 - 07:14
how do you create a table in this doc
User 18/06/2018 - 07:14
i need a 3x3 table
Excelchat Expert 18/06/2018 - 07:15
You please input the data
User 18/06/2018 - 07:15
and two different tabs
Excelchat Expert 18/06/2018 - 07:15
I will set it as a table
Excelchat Expert 18/06/2018 - 07:15
I have created 2 sheets - Sheet 1 & 2
User 18/06/2018 - 07:15
both with tables
User 18/06/2018 - 07:16
it is not letting me insert a table like on excel
User 18/06/2018 - 07:18
i want my table to populate my second sheet once i mark each info as complete with an x
Excelchat Expert 18/06/2018 - 07:19
Google sheet allows that with script
Excelchat Expert 18/06/2018 - 07:19
Can we not do it without table, just with simple range
User 18/06/2018 - 07:20
i want to use an if statement to say if this coolumn is marked x than find this specific part and put in on my pulled inventory sheet
User 18/06/2018 - 07:20
but i want to make it dynamic so i do not have to put the parts in order
Excelchat Expert 18/06/2018 - 07:20
Which sheet is the pulled inventory here?
User 18/06/2018 - 07:20
sheet two
User 18/06/2018 - 07:22
so i can put in different parts at different times so order does not matter
Excelchat Expert 18/06/2018 - 07:22
So you would like to pull anything which is marked X from sheet 1 to Pulled sheet, is that right?
User 18/06/2018 - 07:22
yes
Excelchat Expert 18/06/2018 - 07:23
ok...I think I get your idea.
Excelchat Expert 18/06/2018 - 07:23
Let me formulate it and let's see if that will work for you.
User 18/06/2018 - 07:23
i had a formula i just couldnt get it to be dynamic
Excelchat Expert 18/06/2018 - 07:24
If you can actually share your excel file, it will be of great help.
User 18/06/2018 - 07:24
let me edit it and i will send it.
User 18/06/2018 - 07:24
its not google sheets is thst okay
Excelchat Expert 18/06/2018 - 07:24
Sure...that will be really nice
Excelchat Expert 18/06/2018 - 07:25
Yes, no issues
User 18/06/2018 - 07:25
and will this countdown timer kick me out
Excelchat Expert 18/06/2018 - 07:25
It will automatically extend
User 18/06/2018 - 07:26
okay i will let you know when i finish the doc
Excelchat Expert 18/06/2018 - 07:26
ok
Excelchat Expert 18/06/2018 - 07:27
In the meantime, I have also done a formula here in google sheet cell A2
Excelchat Expert 18/06/2018 - 07:28
This will dynamically pull all those part numbers which has a X in Completed column, when you pull down the formula.
User 18/06/2018 - 07:31
this may take ten minutes is that a problem
Excelchat Expert 18/06/2018 - 07:32
no problem
Excelchat Expert 18/06/2018 - 07:32
But can you quickly have a look at the formula that I have done
Excelchat Expert 18/06/2018 - 07:32
May be that is what you are looking for
User 18/06/2018 - 07:33
i need the quantity to transfer over as well
User 18/06/2018 - 07:33
after i mark x on sheet 1 i need part number and quantity to populate the pulled inventory tab
Excelchat Expert 18/06/2018 - 07:33
yes, it will
User 18/06/2018 - 07:34
its only pulling the part
User 18/06/2018 - 07:34
oh wait i see thats better
Excelchat Expert 18/06/2018 - 07:34
Please have a look now in column B
Excelchat Expert 18/06/2018 - 07:35
You can just drag down this formula till however many rows you have a X in your Sheet1
User 18/06/2018 - 07:35
is it dynamic
Excelchat Expert 18/06/2018 - 07:35
Please note this is an array formula.
Excelchat Expert 18/06/2018 - 07:35
Yes, it is
Excelchat Expert 18/06/2018 - 07:36
You will need to hit Control+Shift+Enter after entering the formula in the first cell of each column, say here cell A2 and B2
User 18/06/2018 - 07:36
so if i choose something from row 33 and mark it with an x it will automatically update the next line of my pulled inventory tab
Excelchat Expert 18/06/2018 - 07:36
Then your formula will get enclosed in a curly bracket.
Excelchat Expert 18/06/2018 - 07:37
yes, absolutely
Excelchat Expert 18/06/2018 - 07:37
You can test it right away
User 18/06/2018 - 07:37
okay how to i transfer this formula to my document
Excelchat Expert 18/06/2018 - 07:38
If you share your file, I can help it out
Excelchat Expert 18/06/2018 - 07:38
Otherwise, you can change the cell references based on your data
Excelchat Expert 18/06/2018 - 07:38
Sheet1!$A:$C is the table on Sheet 1 from where your are pulling your data
Excelchat Expert 18/06/2018 - 07:39
Sheet1!$C:$C is the column you have 'X' on
Excelchat Expert 18/06/2018 - 07:39
ROW(Sheet1!$A:$A) will be the the part number column
Excelchat Expert 18/06/2018 - 07:40
,2) is the column reference based how wide is your table
User 18/06/2018 - 07:40
i will base those changes on what you said and tell you if it worked
Excelchat Expert 18/06/2018 - 07:40
like here we just have 3 columns, so I say 1 for part number and 2 for quantity
Excelchat Expert 18/06/2018 - 07:40
sure
Excelchat Expert 18/06/2018 - 07:40
But please remember to hit Control+Shift+Enter
User 18/06/2018 - 07:41
what does control+shift+enter do
Excelchat Expert 18/06/2018 - 07:41
and you will see curly brackets surrounding your formula which means you have successfully created the array formula
Excelchat Expert 18/06/2018 - 07:42
I am uploading this same file as excel to help you better.
[Uploaded an Excel file]
User 18/06/2018 - 07:42
do i do this before or after the fromula
Excelchat Expert 18/06/2018 - 07:42
After you put int the formula, in the typing mode within the cell you will press the key combo
User 18/06/2018 - 07:43
let me try that one second
User 18/06/2018 - 07:45
for the $A$C is that the headers or just the table
User 18/06/2018 - 07:46
i am wondering if i include the headers or not in my data set
Excelchat Expert 18/06/2018 - 07:46
its the complete table
User 18/06/2018 - 07:46
headers and all?
Excelchat Expert 18/06/2018 - 07:46
yes
User 18/06/2018 - 07:48
and $C$C includes the header and all data correct
Excelchat Expert 18/06/2018 - 07:48
Yes
Excelchat Expert 18/06/2018 - 07:49
Ok..I have created tables in this one
[Uploaded an Excel file]
User 18/06/2018 - 07:51
what is the row, 4:4 at the end
User 18/06/2018 - 07:51
and ,1
Excelchat Expert 18/06/2018 - 07:52
4:4 should show 1:1 at your first row
Excelchat Expert 18/06/2018 - 07:52
It is sequencing all the rows which has an X in it
Excelchat Expert 18/06/2018 - 07:52
And ,1 is the column reference from your table
User 18/06/2018 - 07:52
okay im confused
Excelchat Expert 18/06/2018 - 07:52
Like Part in column A so it say ,1 and then Quantity in column B so ,2
User 18/06/2018 - 07:53
what would 4;4 come from
Excelchat Expert 18/06/2018 - 07:53
ok...you will first enter the formula in row 2, right?
Excelchat Expert 18/06/2018 - 07:53
there it will say Row(1:1)
Excelchat Expert 18/06/2018 - 07:54
When you drag it down, it will say Row(2:2) next, then Row(3:3) below and so on
Excelchat Expert 18/06/2018 - 07:54
Does that make sense?
User 18/06/2018 - 07:54
okay so i just finished the sheet 1 A:A part of my formula
User 18/06/2018 - 07:54
now i write row
User 18/06/2018 - 07:55
then i do what
Excelchat Expert 18/06/2018 - 07:55
and just copy exactly, what I have
Excelchat Expert 18/06/2018 - 07:55
,ROW(1:1)),1),"")
User 18/06/2018 - 07:56
but my page looks different than yours its not an exact copy just a simplified version with many different columns and thousands of rows
Excelchat Expert 18/06/2018 - 07:57
That is the reason I wanted to have a look atleast without data, if those are confidential
User 18/06/2018 - 07:58
i mean i can create that for you if you like but i was so close to finishing i wonndered where exactly that 4:4 came from
User 18/06/2018 - 07:59
like is that on sheet one or pulled sheet
Excelchat Expert 18/06/2018 - 07:59
Your cursor currently on this sheet in cell A5, that is the reason you are seeing 4:4
Excelchat Expert 18/06/2018 - 07:59
You move your cursor to cell A2, you will see Row(1:1)
User 18/06/2018 - 08:00
oh so i literally type 1:1
User 18/06/2018 - 08:00
it might be easier to send
Excelchat Expert 18/06/2018 - 08:00
yep
Excelchat Expert 18/06/2018 - 08:01
You can also select first row after typing Row...whatever works for you
User 18/06/2018 - 08:01
okay give me 10
Excelchat Expert 18/06/2018 - 08:01
That is just to signify that you want the first item which has a X
Excelchat Expert 18/06/2018 - 08:01
We are just left with 10 mins
User 18/06/2018 - 08:02
okay i will try and write it
User 18/06/2018 - 08:02
give me a sec
Excelchat Expert 18/06/2018 - 08:02
The session has auto extended twice, so it wont allow you to extend further.
User 18/06/2018 - 08:06
so 1:1 means from first row to column
User 18/06/2018 - 08:07
mines in row 20
User 18/06/2018 - 08:07
so would it still be 1:1
Excelchat Expert 18/06/2018 - 08:07
what is in row 20?
Excelchat Expert 18/06/2018 - 08:07
We just have 4 mins left
User 18/06/2018 - 08:07
im looking at the 2;2 at the end
Excelchat Expert 18/06/2018 - 08:08
I don't think we can complete it in this session. Would you like to come back for a new session after clearing your file and ready to upload so that we can solve it
User 18/06/2018 - 08:08
is there an email i can finish this at with you or no
Excelchat Expert 18/06/2018 - 08:08
Unfortunately, we do not have that facility.
Excelchat Expert 18/06/2018 - 08:09
Our experts are available here, so you can come back once you are ready.
User 18/06/2018 - 08:09
but it wont be free correct
Excelchat Expert 18/06/2018 - 08:09
It depends if you have subscribed or have you bought just one session.
User 18/06/2018 - 08:10
just one
Excelchat Expert 18/06/2018 - 08:10
Then, I am afraid, you will have to pay for the next session.
User 18/06/2018 - 08:10
okay i guess i will have to figure the rest out on my own thanks for the help
Excelchat Expert 18/06/2018 - 08:11
The array formula that I gave you is a dynamic and little complicated.
Excelchat Expert 18/06/2018 - 08:11
Thanks for your time. Have a great day ahead!

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc