Calculations based on priorities

Hello,

Can someone help with the following please?:

Im trying to do a calculation using a macro. When the user selects the "Make Appointment before" field,
it should automatically calcutate the days left to make an appointment, when the "Refresh" button is clicked.
The calculation is done by first checking the priority and then subtracting registered date from todays date (All data in "Data" sheet)

If the priority is A the user has a maximum of 3 days to create an appointment.
If the priority is B the user has a maximum of 7 days to create an appointment.
If the priority is C the user has a maximum of 14 days to create an appointment.

If its gone past the days it should say how many days behind and change font to bold with minus"-" sign e.g. -1

Also if there's no registered date in data, it should say "No registered date"

Please see attached. Thanks in advance

Hi

As an example, put Make Appointment Before in D4, and Priority in E4. Then run your refresh macro so that it brings back the priority.

If you put the formula

Please Login or Register  to view this content.

in D5, change the number format to be General, and copy down to D12, does it give you the correct value you want for those items that do have a valid date registered?

rylo

Hello,

Thanks for your response

Using your formula I'm able to get the correct value I want for the items that do have a valid date registered

Hi

So does that mean you have got your question solved?

rylo

Hello,

Thanks for your response

It's not really solved, as I dont want the user to have to write the formula.
I want the user to select the "Make Appointment Before" field and put it in any column and when the user hits the Refresh button the days show up.
Also, the user doesn't have to select the priority column. Like the calculation should be done in the "Data" Sheet and the answer shown in the "Make Appointment Before" field.
Hopefully that makes sense

Also, if you dont mind, can you tell me what the "2" does in your formula.

Please Login or Register  to view this content.

Thanks

Bump......

Thanks shg for your help

Hi

Update your Refresh code to be

Please Login or Register  to view this content.

Open your form, and put in a Make Appointment Before column Heading and close out the form.

Then press your refresh button. See how that goes. It doesn't do the formatting that you specifically want, and it doesn't handle the situation where you have a blank date registered, but hopefully it will get you going.

rylo

Hello Rylo,

Thanks a lot for your help.

I have one more query and think I can figure out the rest myself.
When I take out "Date Registered" I get an error saying "Object variable or with block variable not set". I tried changing the code but I'll either get an error or no values, I'm not sure how to fix it

Hi

The formula assumes that you have the Date Registered as a field in the final sheet. If, like the priority it may not exist, then you will have to modify the formula to find it on the data sheet in the same way that I updated the formula to find the priority in the data sheet based on the First Name. You will need to have some sort of unique key that "MUST" be on the final sheet so you can find any required data.

rylo

Hello,

Thanks for your help, I'm able to get it working now.

plank