Using text input box to call cell values.

[mrmatt36]

Looking for info on how to create a text input box on a new sheet that will allow user to input a part number hit a button and have it search data on another sheet and retrieve/display the data related to this item from the other cells.

example- sheet one will have all the part information, column a on sheet one will have the part number, column b will have part name, column c will have part vendor, column d will have the location.
sheet 2 will simply have a text input box with a label - "Input part number to lookup" then have a button "lookup" or "reset" reset will just clear the text box, when you hit lookup it will search column a on sheet one for a match, if matching part number is found it will populate the data for this part from columns a, b, c,& d from sheet one to information box on sheet 2 (same sheet where part number is entered). the text input box and the populated information should be on fixed fields on sheet 2 not a popup box.

 

[dreid1011]

Welcome to the forum.

Does it have to be text boxes? You can do all of that with formulas on the sheet except for the clear/reset button.

 

[mrmatt36]

This is what i am going for: Sheet 2 would be the default sheet when opening the file.

this data would be pulled from sheet 1

 

[mrmatt36]

Welcome to the forum.

Does it have to be text boxes? You can do all of that with formulas on the sheet except for the clear/reset button.

Thanks,
I suppose if what I am after is obtainable just with formulas on the sheet itself, I am not opposed to it. If you can provide some insight on this I can give it a try and see how it works.

Thanks in advance.

 

[dreid1011]

Yeah, give this a try. I didn't do anything to add "clear" functionality. All you have to do it delete the entry in the input box (C7).

Book1
	A	B	C	D
1	Part Number	Part Name	Vendor	Location
2	55531	1/4" stainless washer	national stainless products	Box 42
3	55532	5/16" stainless washer	national stainless products	Box 43
4	55533	3/8" stainless washer	national stainless products	Box 44
5	55534	1/2" stainless washer	national stainless products	Box 45
6	55535	1" drywall screws	grip rite fasteners	Bay 3 - #13
7	55536	1-1/4" drywall screws	grip rite fasteners	Bay 3 - #14
8	55537	1-5/8" drywall screws	grip rite fasteners	Bay 3 - #15
9	55538	6-32 x 3/4" machine screws	national tooling	Shelf 23
10	55539	6-32 x 1" machine screws	national tooling	Shelf 24
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J
1
2					John's Hardware
3					Part Lookup
4
5
6			Part Number
7										Clear
8
9
10
11
12			Part Number:
13
14			Part Name:
15
16			Vendor:
17
18			Location:
19
Sheet2

Cell Formulas
Range	Formula
E12	E12	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$A$2:$A$10,"")
E14	E14	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,"")
E16	E16	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$C$2:$C$10,"")
E18	E18	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$D$2:$D10,"")

 

[mrmatt36]

That seems to work. is there a way to have a response if the number input is not found on sheet 1? "item not found" "check part number and try again" something to that affect? Also if there away when it grabs the data from a cell from sheet 1 if data matches certain words to change them on the display? IE way to mask the vendor info. Item 55533 vendor = national stainless products. could we make it so it would respond to sheet 2 with "vendor #1", if value = grip rite fasteners then respond with vendor = Vendor #2 etc.

 

[dreid1011]

This would be the easiest way to mask the vendors by adding a new column to sheet 1 with the desired values:

Book1
	A	B	C	D	E
1	Part Number	Part Name	Vendor	Location	Vendor Mask
2	55531	1/4" stainless washer	national stainless products	Box 42	Vendor #1
3	55532	5/16" stainless washer	national stainless products	Box 43	Vendor #1
4	55533	3/8" stainless washer	national stainless products	Box 44	Vendor #1
5	55534	1/2" stainless washer	national stainless products	Box 45	Vendor #1
6	55535	1" drywall screws	grip rite fasteners	Bay 3 - #13	Vendor #2
7	55536	1-1/4" drywall screws	grip rite fasteners	Bay 3 - #14	Vendor #2
8	55537	1-5/8" drywall screws	grip rite fasteners	Bay 3 - #15	Vendor #2
9	55538	6-32 x 3/4" machine screws	national tooling	Shelf 23	Vendor #3
10	55539	6-32 x 1" machine screws	national tooling	Shelf 24	Vendor #3
Sheet1

Book1
	A	B	C	D	E	F	G	H
1
2					John's Hardware
3					Part Lookup
4
5
6			Part Number
7			55537
8
9
10
11
12			Part Number:		55537
13
14			Part Name:		1-5/8" drywall screws
15
16			Vendor:		Vendor #2
17
18			Location:		Bay 3 - #15
19
Sheet2

Cell Formulas
Range	Formula
E12	E12	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$A$2:$A$10,IF(C7="","","Item Not Found"))
E14	E14	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,IF(C7="","","Item Not Found"))
E16	E16	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$E$2:$E$10,IF(C7="","","Item Not Found"))
E18	E18	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$D$2:$D10,IF(C7="","","Item Not Found"))

And try this for the "item not found" message:

Book1
	A	B	C	D	E	F	G	H
1
2					John's Hardware
3					Part Lookup
4
5
6			Part Number
7			4485
8
9
10
11
12			Part Number:		Item Not Found
13
14			Part Name:		Item Not Found
15
16			Vendor:		Item Not Found
17
18			Location:		Item Not Found
19
Sheet2

Cell Formulas
Range	Formula
E12	E12	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$A$2:$A$10,IF(C7="","","Item Not Found"))
E14	E14	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,IF(C7="","","Item Not Found"))
E16	E16	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$C$2:$C$10,IF(C7="","","Item Not Found"))
E18	E18	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$D$2:$D10,IF(C7="","","Item Not Found"))

Or this if you don't want the message repeated in each field:

Book1
	A	B	C	D	E	F	G	H
1
2					John's Hardware
3					Part Lookup
4
5
6			Part Number
7			55579
8
9
10
11
12			Part Number:		Item Not Found
13
14			Part Name:
15
16			Vendor:
17
18			Location:
19
Sheet2

Cell Formulas
Range	Formula
E12	E12	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$A$2:$A$10,IF(C7="","","Item Not Found"))
E14	E14	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,"")
E16	E16	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$E$2:$E$10,"")
E18	E18	=XLOOKUP(C7,Sheet1!$A$2:$A$10,Sheet1!$D$2:$D10,"")

 

[mrmatt36]

The problem with that is the data on sheet one is often updated and includes hundreds of different vendors. the data on sheet one is also supplied from a different department without that column or masking.

 

[dreid1011]

The problem with that is the data on sheet one is often updated and includes hundreds of different vendors. the data on sheet one is also supplied from a different department without that column or masking.

Then you could use a separate table with unique vendor names and their masks. Then all you would have to do is update that list. Or we could potentially make that a more automatic process too.

 

[Skyybot]

That seems to work. is there a way to have a response if the number input is not found on sheet 1? "item not found" "check part number and try again" something to that affect? Also if there away when it grabs the data from a cell from sheet 1 if data matches certain words to change them on the display? IE way to mask the vendor info. Item 55533 vendor = national stainless products. could we make it so it would respond to sheet 2 with "vendor #1", if value = grip rite fasteners then respond with vendor = Vendor #2 etc.

If Vendor data needs to be masked, why not remove the Vendor Textbox?