# Is there a way to link cells to generate an alarm?

Copper Contributor

# Is there a way to link cells to generate an alarm?

Hey y'all!

I'm not an Excel expert (barely proficient) but I'm trying to make a spreadsheet for patient medications that will link "Reorder" column to the patient "appointment date" column, and trigger a flag.  (I'm using the "Inventory list with highlighting" template.)

Is this doable or do I need to input each time?

Thanks for any and all help!

# Re: Is there a way to link cells to generate an alarm?

Yes, it is doable to link cells in Excel to generate a flag or alarm. You can achieve this by using conditional formatting and possibly some helper columns or formulas. Here is a step-by-step guide to set this up in your "Inventory list with highlighting" template:

Step-by-Step Guide

• Columns: Make sure you have columns for the patient’s name, medication name, reorder date, and appointment date.

2. Calculate the Difference Between Dates

• Add a new column to calculate the difference between the reorder date and the appointment date.

For example, if your "Reorder Date" is in column D and your "Appointment Date" is in column E, add a new column F titled "Days Until Appointment" with the following formula in F2:

=E2-D2

This formula calculates the number of days between the reorder date and the appointment date.

3. Apply Conditional Formatting

• Select the range of the new column "Days Until Appointment" (e.g., F2).
• Go to the Home tab.
• Click on Conditional Formatting > New Rule.
• Choose Use a formula to determine which cells to format.
• Enter the formula to check if the difference is within a certain threshold (e.g., less than 7 days).

=\$F2<7

• Click on Format, set the desired formatting (e.g., fill color, font color), and click OK.

This will highlight the cells in the "Days Until Appointment" column if the difference is less than 7 days.

4. Add a Flag Column (Optional)

• If you want to add a separate flag column, create another column titled "Reorder Flag" in column G.
• In G2, use an IF formula to generate a flag (e.g., "Reorder") when the condition is met:

=IF(F2<7, "Reorder", "")

This formula will display "Reorder" in column G if the difference between the reorder date and the appointment date is less than 7 days.

5. Combine with Conditional Formatting for Visual Flags

• You can apply conditional formatting to the "Reorder Flag" column as well, using similar steps as above, to make the "Reorder" text stand out more.

Example Layout:

 Patient Name Medication Reorder Date Appointment Date Days Until Appointment Reorder Flag John Doe Med A 2024-07-10 2024-07-15 5 Reorder Jane Smith Med B 2024-07-08 2024-07-25 17

To automate alerts further, you could use Excel's data validation, or even VBA, to pop up messages. However, for basic needs, conditional formatting and flag columns should suffice.

By following these steps, you'll be able to create a dynamic and visually intuitive system to flag when medication reorder dates are approaching patient appointment dates, helping you manage patient medication schedules effectively. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!