highlight when a patient needs to be transferred to another hospital

[lollypop1389]

Hi All,

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 There are 9 in total and these are saved as a column in another tab. 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 selected 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

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I have done it for you this time.)

[AliGW]

This can be done with a combination of conditional formatting rules and the EDATE function.

I suggest you provide a simplified workbook listing the combinations you are going to need so that we can show you how to create the rules which you can then apply to your own workbook, thus learning how it works.

[lollypop1389]

Hi,

Thank you for your response. I'm really sorry if I am not getting things right, I am really quite confused by Excel and have been really struggling to get my head around all of the terminology. I have attached a simplified spreadsheet, I have used these specific columns (Sheet 1 G+H, Sheet 2 C+D)as that is where it sits within my main spreadsheet just to help with my understanding. The months required per treatment are set out in the columns in sheet 2. Ideally the formula will apply to the whole of columns G+H for future inputting.

I guess the combinations will be:

1) If "Surgery" is selected in column G, the row will be highlighted 2 months from the start date listed in Column H
2) If "Radiotherapy" is selected in column G, the row will be highlighted 6 months from the start date listed in Column H
3) If "Chemo/Radiotherapy" is selected in column G, the row will be highlighted 6 months from the start date listed in Column H
4) If "Chemotherapy" is selected in column G, the row will be highlighted 6 months from the start date listed in Column H
5) If "Monitor" is selected in column G, the row will be highlighted 1 month from the start date listed in Column H
6) If "Transfer Care" is selected in column G, the row will be highlighted 1 month from the start date listed in Column H
7) If "Palliative Radiotherapy" is selected in column G, the row will be highlighted 4 months from the start date listed in Column H
8) If "Palliative Chemotherapy" is selected in column G, the row will be highlighted 4 months from the start date listed in Column H
9) If "BSC" is selected in column G, the row will be highlighted 1 month from the start date listed in Column H

Many Thanks

Laura

[AliGW]

OK - you were sort of on the right track!

I have used this rule:

=AND(H2<>"",TODAY()>=EDATE(H2,VLOOKUP(G2,$B$2:$C$10,2,0)))

The lookup table is here:

Excel 2016 (Windows) 32 bit

	B	C
1	Primary Treatment	Months
2	Monitor	1
3	BSC	1
4	Transfer Care	1
5	Surgery	2
6	Palliative Radiotherapy	4
7	Palliative Chemotherapy	4
8	Radiotherapy	6
9	Chemo/Radiotherapy	6
10	Chemotherapy	6

Sheet: Sheet1

This can go on another sheet, which can be hidden if you wish.

[AliGW]

Administrative Note:

Rather annoying, this, having spent a considerable amount of time helping you ...

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I will provide the link for you today: https://www.mrexcel.com/board/thread...mulas.1139422/.)