Help with Formulas

[lollypop1389]

Hi All,

I was wondering if I could have some help in creating a formula, I have been struggling for days trying to figure out if this is even achievable. I'm trying to create a way to highlight when a patient needs to be transferred to another hospital.

In column G I have a drop down list of treatment options e.g. surgery, radiotherapy etc. In column H I have the date which treatment started on e.g. 03/03/2020. Is there a way to create a formula/conditional formatting rule wherein if "Surgery" is written in Column G, and the start date in Column H is "today minus 2 months", then it will highlight that row. Similarly if "Radiotherapy" is written in Column G, then the start date should trigger the row to be highlighted after 6 months time.

I would be really grateful for any help or advice. I am really struggling and would like to know if this is even achievable or if I am wasting my time trying to figure it all out.

Many Thanks

 

[Toadstool]

Hi Lollypop1389,

You don't say how many treatment options you have so I'll assume your Data Validation List is in a table then I'll add a column for the months trigger (columns L and M which can be hidden or on another tab).

Lollypop1389.xlsx
	G	H	I	J	K	L	M
1	Treatment Option	Treatment Started				Treatments	Months
2	Surgery	08-May-20				Surgery	2
3	Radiotherapy	05-May-20				Radiotherapy	6
4	Physical Therapy	06-Jun-20				Physical Therapy	4
5	Reiki	08-Jun-20				Reiki	1
6	Radiotherapy	01-Jan-20
7	Surgery	09-May-20
8
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:H9	Expression	=EDATE($H2,(INDEX($M$2:$M$99,MATCH($G2,$L$2:$L$99,0))))<=TODAY()	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G2:G7	List	=$L$2:$L$5

 

[lollypop1389]

Hi Toadstool, Wow thank you so much for this!! I have a column in another spreadsheet for the treatment options and there are 9 options.
The way that you have done the treatment options and the months, do I just need to copy and paste your formula in (replacing with my column letters) essentially?

 

[lollypop1389]

**sorry, meant to say the columns are in another tab, not another spreadsheet!

 

[Toadstool]

You'll need to point the formula at the other sheet and columns.

Here I have the Data Validation list on Sheet2 in columns C and D

Lollypop1389.xlsx
	C	D
1	Treatments	Months
2	Surgery	2
3	Radiotherapy	6
4	Physical Therapy	4
5	Reiki	1
6	Treat 5	5
7	Treat 6	6
8	Treat 7	7
9	Treat 8	8
10	Treat 9	9
Sheet2

The Sheet1 entry has to change the formulae to point at the other sheet:

Lollypop1389.xlsx
	G	H
1	Treatment Option	Treatment Started
2	Surgery	08-May-20
3	Radiotherapy	05-May-20
4	Physical Therapy	06-Jun-20
5	Reiki	08-Jun-20
6	Radiotherapy	01-Jan-20
7	Surgery	09-May-20
8
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:H99	Expression	=EDATE($H2,(INDEX(Sheet2!$D$2:$D$10,MATCH($G2,Sheet2!$C$2:$C$10,0))))<=TODAY()	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G2:G9	List	=Sheet2!$C$2:$C$10

 

[lollypop1389]

I've tried to add the formula but it is not working. The tables are on another tab as below.

 

[Toadstool]

I've changed the sheet names to match yours. I note you have tables for the dropdown but as I can't be sure of your table names I've just used standard addressing.

Lollypop1389.xlsx
	C	D
1	Primary Treatment	Column 2
2	Surgery	3
3	Radiotherapy	6
4	Chemo/Radiotherapy	6
5	Chemotherapy	6
6	Monitor	1
7	Transfer Care	1
8	Palliative Radiotherapy	4
9	Palliative Chemotherapy	4
10	BSC	1
Drop Down Menus

I've tried to match your row setup for Main Database

Lollypop1389.xlsx
	G	H
1	Primary Treatment	Date P. Treatment
2
3
4	TBC
5	Surgery	08-Apr-20
6	Chemo/Radiotherapy	05-May-20
7	Monitor	06-Jun-20
8	Palliative Radiotherapy	08-Jun-20
9	Radiotherapy	01-Jan-20
10	Surgery	09-Apr-20
11
Main Database

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5:H102	Expression	=EDATE($H5,(INDEX('Drop Down Menus'!$D$2:$D$10,MATCH($G5,'Drop Down Menus'!$C$2:$C$10,0))))<=TODAY()	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G5:G11	List	='Drop Down Menus'!$C$2:$C$10

 

[lollypop1389]

Hi Toadstool,

Thank you so much I really do appreciate your help. I am still really struggling to get this to work. Would it be possible/is it allowed to send you the spreadsheet at all. I just don't understand why I can get it to work for me.

 

[Toadstool]

The MrExcel crew don't like posted issues being worked outside of the forum but if you can make sure any personal data is anonymized and provide it via some media such as Dropbox then I, and others, can resolve your challenge.

 

[Fluff]

Cross posted highlight when a patient needs to be transferred to another hospital

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.