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.
