Excel - Email Marco - Conditional Format Choose Specific Rows of Data to Email

IN PROGRESS
Bids
13
Avg Bid (AUD)
N/A
Project Budget (AUD)
$30 - $250

Project Description:
I HAVE DATA BEING KEYED INTO A CUSTOMER RETURN ADVICE (CRA) LOG IN EXCEL
THE USER IS TO MANUALLY KEY IN THE DATE GOODS ARE RECEIVED (COLUMN B)

THE DUE DATE (COLUMN F) ALREADY HAS A FORMULA IN IT TO CALCULATE 14 DAYS FROM THE DATE THE GOODS ARE RECEIVED (COLUMN B)

THE ROWS TO BE CONDITIONALLY FORMATTED - IF THE DUE DATE (COLUMN F) IS IN THE PAST (BEFORE TODAYS DATE) THE ENTIRE ROW IS TO FILL RED AND THE FONT IS TO BE WHITE AND BOLD (REFER TO ROW 6 IN ATTACHED FILE AS EXAMPLE)

AND - IF THE DUE DATE (COLUMN F) IS IN THE PAST (BEFORE TODAYS DATE) HOWEVER THE USER HAS MANUALLY KEYED IN A REVISED DATE (COLUMN G) THEN THE ROW STAYS NORMAL AND DOES NOT CHANGE COLOR - (REFER TO ROW 7 IN THE ATTACHED FILE AS AN EXAMPLE)

AND IF THE DUE DATE (COLUMN F) IS IN THE PAST (BEFORE TODAYS DATE) AND THE USER HAS MANUALLY KEYED IN A REVISED DATE (COLUMN G) HOWEVER THE REVISED DATE IS NOW ALSO IN THE PAST (BEFORE TODAYS DATE) THE ENTIRE ROW IS TO FILL RED AND THE FONT IS TO BE WHITE AND BOLD (REFER TO ROW 8 IN ATTACHED FILE AS EXAMPLE)

AND WHENEVER THE USER HAS MANUALLY KEYED IN A DATE COMPLETE (COLUMN J) THEN NO MATER WHAT DATES ARE IN THE DUE DATE (COLUMN F) AND REVISED DATE (COLUMN G) THE ENTIRE ROW IS TO BE FILLED GREEN WITH BLACK FONT AND BOLD) - REFER TO ROW 9 IN ATTACHED FILE AS AN EXAMPLE)

THE CUSTOMER COLUMN (COLUMN C) IS TO HAVE DATA VALIDATION - WITH A DROP DOWN LIST FROM DATA STORED IN COLUMN A FROM THE SHEET TAB TITLED ''CLIENT ALLOCATION' - THIS LIST WILL BE AROUND 2000 CELLS LONG AND NEED TO BE ABLE TO ADD TO IT

WHEN THE CUSTOMER HAS BEEN SELECTED THEN THE SALES PERSON (COLUMN D) IS TO AUTO FILL USING VLOOKUP WITH DATA STORED IN COLUMN A AND B FROM THE SHEET TAB TITLED ''CLIENT ALLOCATION'

THE REVISED DATE COLUMN (COLUMN H) DUE DATE COLUMN (COLUMN F) AND SALES PERSON COLUMN (COLUMN D)IS TO BE PASSWORD PROTECTED WITH THE PASSWORD IIMSCRA - SO ONLY PARTICULAR USES CAN KEY IN A REVISED DATE

THE LAST EMAIL COLUMN (COLUMN L) IS TO HAVE A CHECK BOX (OR SOMETHING SIMILAR) SO THAT IF THE CHECK BOX IS CHECKED THEN THE USER CAN THEN CLICK ON THE 'EMAIL' BUTTON ABOVE (J2) - WHEN THIS EMAIL BUTTON IS CLICKED IT TAKES THE DATA KEYED IN THAT ROW THAT IS CHECKED AND POPULATES THAT DATA INTO THE CORRSPONDING CELLS IN THE SHEET TAB TITLED CRA FORM - THE CRA FORM HAS THE SAME CELL TITLES AS THE CRA LOG SHEET SO THE DATA GOES INTO ITS CORRESPONDING FIELD IN THE CRA FORM SHEET - AND THEN SENDS THAT CRA FORM AS A SEPARATE SPREAD SHEET IN AN EMAIL

THE EMAIL ADDRESS TO SEND THE CRA FORM TO IS BASED ON THE EMAIL ADDRESS ASSIGNED TO THE SALES PERSON (COLUMN D) THAT IS STORED IN COLUMN C OF THE SHEET TAB TITLED CLIENT ALLOCATION - I WANT TO BE ABLE TO ADD OTHER EMAIL ADDRESSES INTO OUTLOOK BEFORE THE THE EMAIL SENDS - SO OUTLOOK IS TO OPEN WITH THE SALES PERSONS EMAIL ADDRESS IN IT AND THE CRA FORM ATTACHED BUT I HAVE TO PHYSICALLY CLICK SEND TO SEND THE EMAIL FROM OUTLOOK

Skills required:
Excel
Qualifications required:
us_eng_1 US English - Level 1
Additional Files: Customer Return Advice (2).xlsx
About the employer:
Verified
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.


$ 147
in 0 days
$ 200
in 3 days
$ 184
in 7 days
Hire bouslimi1979
$ 111
in 3 days
Hire maverick44000
$ 200
in 5 days
$ 200
in 7 days
$ 100
in 3 days
$ 150
in 0 days
$ 105
in 3 days
Hire excel4engineers
$ 50
in 2 days