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