Two columns one uodates the other

[Manda1967]

I have a spreadsheet that I put patient referrals innto
I put the ward number in one column and in the next the specially of that ward
The list of wards and specialties are in my excel so I can check and make sure I match the riggt ward to specially

Is there a way to do this so that the second column pics up the specially automatically from the list?

Hope that makes sense and hope you can help
Thank you

 

[Asbestos_Jen]

I think this is what you're after.

Book1
	B	C	D	E	F	G	H
1	List of wards and specialties
2	Ward	Specialty			Patient	Ward	Specialty
3	1	a			Jason Hamilton	8	h
4	2	b			Kristy Deschenes	3	c
5	3	c			Kim Jobin	2	b
6	4	d			Meghan Beaudry	1	a
7	5	e			Jean-Francois Arsenault	3	c
8	6	f			Jean-Guy Gray	4	d
9	7	g			Lyn Nguyen	7	g
10	8	h			Jonathan Evans	8	h
11	9	i			Nathalie Small	6	f
12	10	j			Shawna Donaldson	2	b
13					Cindy McCormick	10	j
14					Elise Pike	9	i
15					Pascale Marsh	1	a
16					Duncan Gaudet	4	d
17					Mary Legare	4	d
Sheet1

Cell Formulas
Range	Formula
H3:H17	H3	=VLOOKUP(G3,$B$3:$C$12,2)

 

[Manda1967]

I think this is what you're after.

Book1
	B	C	D	E	F	G	H
1	List of wards and specialties
2	Ward	Specialty			Patient	Ward	Specialty
3	1	a			Jason Hamilton	8	h
4	2	b			Kristy Deschenes	3	c
5	3	c			Kim Jobin	2	b
6	4	d			Meghan Beaudry	1	a
7	5	e			Jean-Francois Arsenault	3	c
8	6	f			Jean-Guy Gray	4	d
9	7	g			Lyn Nguyen	7	g
10	8	h			Jonathan Evans	8	h
11	9	i			Nathalie Small	6	f
12	10	j			Shawna Donaldson	2	b
13					Cindy McCormick	10	j
14					Elise Pike	9	i
15					Pascale Marsh	1	a
16					Duncan Gaudet	4	d
17					Mary Legare	4	d
Sheet1

Cell Formulas
Range	Formula
H3:H17	H3	=VLOOKUP(G3,$B$3:$C$12,2)

I think this is what you're after.

Book1
	B	C	D	E	F	G	H
1	List of wards and specialties
2	Ward	Specialty			Patient	Ward	Specialty
3	1	a			Jason Hamilton	8	h
4	2	b			Kristy Deschenes	3	c
5	3	c			Kim Jobin	2	b
6	4	d			Meghan Beaudry	1	a
7	5	e			Jean-Francois Arsenault	3	c
8	6	f			Jean-Guy Gray	4	d
9	7	g			Lyn Nguyen	7	g
10	8	h			Jonathan Evans	8	h
11	9	i			Nathalie Small	6	f
12	10	j			Shawna Donaldson	2	b
13					Cindy McCormick	10	j
14					Elise Pike	9	i
15					Pascale Marsh	1	a
16					Duncan Gaudet	4	d
17					Mary Legare	4	d
Sheet1

Cell Formulas
Range	Formula
H3:H17	H3	=VLOOKUP(G3,$B$3:$C$12,2)

Oh wow it definitely is. Now here come the questions
Can my ward and specialty list be on a separate tab?
And how the heck do I create that formula?
Amanda

 

[Asbestos_Jen]

Can my ward and specialty list be on a separate tab?

Absolutely. If you use the XL2BB tool to send a small sample of your setup, we can work with the relevant cells. You can copy the formula I used above and adjust the cell references as needed:

=VLOOKUP(G3,$B$3:$C$12,2)

It might look like this if your patient record is on sheet1 and your list is on sheet2:

2023-12-20.xlsx
	F	G	H
2	Patient	Ward	Specialty
3	Jason Hamilton	8	h
4	Kristy Deschenes	3	c
5	Kim Jobin	2	b
6	Meghan Beaudry	1	a
7	Jean-Francois Arsenault	3	c
8	Jean-Guy Gray	4	d
9	Lyn Nguyen	7	g
10	Jonathan Evans	8	h
11	Nathalie Small	6	f
12	Shawna Donaldson	2	b
13	Cindy McCormick	10	j
14	Elise Pike	9	i
15	Pascale Marsh	1	a
16	Duncan Gaudet	4	d
17	Mary Legare	4	d
18
19
Sheet1

Cell Formulas
Range	Formula
H3:H17	H3	=VLOOKUP(G3,Sheet2!$B$4:$C$13,2)

2023-12-20.xlsx
	B	C
2	List of wards and specialties
3	Ward	Specialty
4	1	a
5	2	b
6	3	c
7	4	d
8	5	e
9	6	f
10	7	g
11	8	h
12	9	i
13	10	j
Sheet2

 

[Manda1967]

Hi and apologies for not replying sooner this has all been so helpful - truly - but I am a novice at all this stuff eek so being unable to XL2BB especially on my work laptop - I have taken a screen grab of the columns and it is the two highlighted ones at the end
Referral ward I would put in then for the CSU of referral ward to automatically update (that data is in my filters sheet in excel just a column of all the filters I use in this main sheet - I keep them seperate as it is a big sheet

This is the column on my filters page:

I do hope this makes sense and I will be eternally grateful for any advice and coding offered Amanda

 

[Asbestos_Jen]

We will need to see the Referral Ward list as well, so we can see how the two lists relate to each other. If you can copy and paste them as tables, that would help, too.

 

[Manda1967]

of course so I have taken a section but the ward list is in column A and the CSU list is in column B

A	B
Ward	CSU
C01	CAH
C02	CAH
C03	CAH
C04	CAH
C05	CAH
C06	CAH
J01/NICU	Children's Services
J03	Women's Services
J04	Women's Services
J05	Women's Services
J06	Cardio
J07	Urgent Care
J08	Urgent Care
J09	Cardio
J10	Cardio
J11	Cardio
J12	Cardio
J14	SIM
J15	SIM
J16	SIM
J17	SIM
J19	SIM
J20	SIM
J21	SIM

 

[Asbestos_Jen]

Ok, if your data entry table looks like the first set, and the Ward list is in columns A and B of a sheet called "Filters" it would look like this. You can change the cell references to suit if they don't exactly match. For instance, if you have a list of 45 wards instead of the 25 you provided, just change he formula in entry table column P from $B$26 to $B$46.

Specialties and Wards.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Patient Name	Date of Birth	Age	NHSno.	Gender	Sensory Support Needs	Ethnicity	Post code	Date of Alert	Primary Support Reason	Alleged Primary Abuse Type	PATCH	Location of alleged abuse	CSU Location of Alleged Abuse	Referral ward	CSU of referral ward
2	Jason Hamilton														C01	CAH
3	Kristy Deschenes														C04	CAH
4	Kim Jobin														C05	CAH
5	Meghan Beaudry														J04	Women's Services
6	Jean-Francois Arsenault														J08	Urgent Care
7	Jean-Guy Gray														J11	Cardio
8	Lyn Nguyen														J15	SIM
9	Jonathan Evans														J19	SIM
10	Nathalie Small														C02	CAH
11	Shawna Donaldson														C03	CAH
12	Cindy McCormick														J01/NICU	Children's Services
13	Elise Pike														J10	Cardio
14	Pascale Marsh														J14	SIM
15	Duncan Gaudet														J17	SIM
16	Mary Legare														J20	SIM
17	Casey Duchesne														J11	Cardio
18	Jason Joseph														J12	Cardio
19	Corey Ryan														J14	SIM
20	Lou Baird														J15	SIM
21	Terri Liang														J16	SIM
22	Bruce Letourneau														J17	SIM
23	Earl Guerin														J19	SIM
24	Angelo Lindsay														J20	SIM
25	Catherine Turnbull														J21	SIM
26	Dean Legault														J21	SIM
27	Elmer Wu														J21	SIM
28	Edna McCann														J01/NICU	Children's Services
29	Carolyn Andrews														J21	SIM
30	Francois Morton														J10	Cardio
31	Mandy Barker														J17	SIM
Sheet1

Cell Formulas
Range	Formula
P2:P31	P2	=VLOOKUP(O2,Filters!$A$1:$B$26,2)

Specialties and Wards.xlsx
	A	B
1	A	B
2	Ward	CSU
3	C01	CAH
4	C02	CAH
5	C03	CAH
6	C04	CAH
7	C05	CAH
8	C06	CAH
9	J01/NICU	Children's Services
10	J03	Women's Services
11	J04	Women's Services
12	J05	Women's Services
13	J06	Cardio
14	J07	Urgent Care
15	J08	Urgent Care
16	J09	Cardio
17	J10	Cardio
18	J11	Cardio
19	J12	Cardio
20	J14	SIM
21	J15	SIM
22	J16	SIM
23	J17	SIM
24	J19	SIM
25	J20	SIM
26	J21	SIM
Filters