Need some help

[Hofver]

Hi!

I need help with my excel worksheet.

If I write XXXX in column A - YYYY will appear in column b.
Is that possible?

 

[AlanY]

put =if(A1="XXXX","YYYY","") in B1 and copy down

 

[Hofver]

put =if(A1="XXXX","YYYY","") in B1 and copy down

Hmm, sorry that won't work.

We have 17 workorder number which is connected to 17 PO numbers.

(WO 1901 - PO 0101
1902 - 0102
1903 - 0103 etc etc)

So if I write WO number "1901" in column A - PO Number "0101" will be written in column b. Would save alot of time!

 

[AlanY]

ok, try vlookup() with a table like this

Book1
	A	B	C	D	E
1	1905	0105		WO	PO
2				1901	0101
3				1902	0102
4				1903	0103
5				1904	0104
6				1905	0105
7				1906	0106
8				1907	0107
9				1908	0108
10				1909	0109
11				1910	0110
12				1911	0111
13				1912	0112
14				1913	0113
15				1914	0114
16				1915	0115
17				1916	0116
18				1917	0117
Sheet1

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,$D$2:$E$18,2,FALSE)

 

[AlanY]

or just ="01"&RIGHT(A1,2) in B1 might work

 

[Hofver]

ok, try vlookup() with a table like this

	A	B	C	D	E
WO	PO

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1905[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0105[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1901[/TD]
[TD="align: right"]0101[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1902[/TD]
[TD="align: right"]0102[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1903[/TD]
[TD="align: right"]0103[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1904[/TD]
[TD="align: right"]0104[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1905[/TD]
[TD="align: right"]0105[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1906[/TD]
[TD="align: right"]0106[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1907[/TD]
[TD="align: right"]0107[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1908[/TD]
[TD="align: right"]0108[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1909[/TD]
[TD="align: right"]0109[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1910[/TD]
[TD="align: right"]0110[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1911[/TD]
[TD="align: right"]0111[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1912[/TD]
[TD="align: right"]0112[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1913[/TD]
[TD="align: right"]0113[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1914[/TD]
[TD="align: right"]0114[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1915[/TD]
[TD="align: right"]0115[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1916[/TD]
[TD="align: right"]0116[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1917[/TD]
[TD="align: right"]0117[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=VLOOKUP(A1,$D$2:$E$18,2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you, this did the work.
Is it possible to extend the script to lookup from another sheet? Im creating new WO with unique number that is also connected to PO numbers.

Example: WO: 19301 - 0107
19409 - 0117

 

[Hofver]

Couldn't delete my previous post, but here's an better explanation. I hope

 

[Hofver]

https://imgur.com/nP1yEi4

 

[AlanY]

in that case, its better to define the table as range then it can be put anywhere in the wookbook.

table now defined as =Sheet4!$A$1:$B$18

Book1
	A	B
1	1905	0105
2	1917	0117
Sheet1

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,Table,2,FALSE)

Named Ranges
Name	Refers To	Cells
Table	=Sheet4!$A$1:$B$18

Book1
	A	B
1	WO	PO
2	1901	0101
3	1902	0102
4	1903	0103
5	1904	0104
6	1905	0105
7	1906	0106
8	1907	0107
9	1908	0108
10	1909	0109
11	1910	0110
12	1911	0111
13	1912	0112
14	1913	0113
15	1914	0114
16	1915	0115
17	1916	0116
18	1917	0117
Sheet4