Help needed in finding a correct formula.

[kerry9439]

Hi Everyone,
I am using excel at work (I work in a maternity department at my local hospital) and need to find a formula that as i input my data, excel would automatically put my patients names and EDD (Estimated Delivery Date) on another sheet in the same workbook and then work out a date when each patient will be 36 weeks.

 

[alansidman]

Show us a sample worksheet (anonymized) with 6-8 patients. Show us what you have and then what you expect for a mocked up solution so that we can provide you with a specific solution. Please use XL2BB as described in my signature to upload the data.

 

[kerry9439]

Show us a sample worksheet (anonymized) with 6-8 patients. Show us what you have and then what you expect for a mocked up solution so that we can provide you with a specific solution. Please use XL2BB as described in my signature to upload the data.

GDM Database Example.xlsx
	A
8
GDM Database

GDM Database Example.xlsx
	C
12
36 Week List

So I would need the names from the Name column and the dates from the EDD Column of the patients on the "GDM Database sheet" to automatically copy over onto the "36 week List" that is on the same workbook and to continue to copy their name and edd's to the "36 week List" when i add new patients. But i would also like excel to work out the date that each patient will be 36 weeks and also do this automatically every time i add a patient on the "GDM Database Sheet"
Hope this Makes sense.
Thank You
Kerry

 

[kerry9439]

Show us a sample worksheet (anonymized) with 6-8 patients. Show us what you have and then what you expect for a mocked up solution so that we can provide you with a specific solution. Please use XL2BB as described in my signature to upload the data.

GDM Database Example.xlsx
	A
8
GDM Database

GDM Database Example.xlsx
	C
12
36 Week List

So I would need the names from the Name column and the dates from the EDD Column of the patients on the "GDM Database sheet" to automatically copy over onto the "36 week List" that is on the same workbook and to continue to copy their name and edd's to the "36 week List" when i add new patients. But i would also like excel to work out the date that each patient will be 36 weeks and also do this automatically every time i add a patient on the "GDM Database Sheet"
Hope this Makes sense.
Thank You
Kerry

GDM Database Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Date Of Referral	Date First Seen	Name	Date of Birth	Hosp Number	EDD	1st Hba1c	1st Fasting Blood Glucose	Gestation	2nd Hba1c	2nd Fasting Blood Glucose	Gestation	3rd Hba1c	3rd Fasting Blood Glucose	Gestation	ALT/AST	Is the result abnormal by nice guidelines	Previous GDM Having Test	BMI	Insulin Start	Metformin start	Date Delivered	Gest	SVD/LSCS/FORCEPS	Weight	Gender	IOL	NICU
2		January
3		02/01/2020	Annabel Smith	28/10/2000	D159824	14/04/2020	40	5.8	24+2								Yes	No	32.2			07/04/2020	39+0	SVD	3.995kg	Male	Yes	No
4		02/01/2020	Snow White	19/05/1992	D123456	25/08/2020	Previous GDM wishes to assume										N/A	No	28.4		20/06/2020	04/08/2020	37+0	LSCS	3.552kg	Female	No	Yes
5		04/01/2020	Janice Baker	30/06/1996	D147852	25/04/2020	44	6.5	24+5	43	5.3	36+2					Yes	Yes	35.4	27/03/2020		06/04/2020	37+2	forceps	4.511kg	Female	Yes	No
6		04/01/2020	Julie smith	16/10/1989	D123456	15/04/2020	39	5.2	24+1								No	No	30.5
7		04/01/2020	Jasmine Price	25/09/1999	D654321	16/04/2020	48	6.7	24+4							ALT 18 / AST 15	Yes	No	25.2
8		06/01/2020	Kerry Conolly	14/04/1986	D789321	23/07/2020	Previous GDM wishes to assume										N/A	No	29.8
GDM Database

GDM Database Example.xlsx
	A	B	C
1	Name	EDD	36 Weeks
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
36 Week List

I don't think I copied the first one in right, this one should be better

Kerry

 

[maabadi]

write at A2 (in 36 Week List):
='GDM database'!C3
write at B2 :
='GDM database'!F3
or if you want to write name at another order at column A, Use:
=VLOOKUP(A2,'GDM database'!$C$3:$F$999,4,FALSE)
you should change 999 with your last row number have data.
write at C2 ( and change column format to date):
=B2+252

and finally drag all 3 column to last cell

 

[kerry9439]

Thank You, it worked only thing i had to change was the calculation in c2 as the 36 week date wasn't right. I changed it to =b2-28 and it comes up with the right date.
Thank you again
Kerry

 

[maabadi]

You 're Welcome.