Automatic Task Display

Hi Guys n Girls,

Wonderful site - hopefully someone will be able to solve my little dilemma.

I am creating a new sheet for my processing team, and want to have a the next task for each client apprear automatically. This occurs when a new date is entered along the row. I created a IF statement that works quite well (while being ugly), but i will need more than 7 nested formulas to cover all of the tasks they do.

=IF(E2="","Follow up Relevant Pages",IF(F2="","Follow up Submission",IF(G2="","Chase AIP",IF(I2="","Chase O/S & Follow up Vals",IF(K2="","Follow Up FA",IF(L2="","Follow Up LDS",IF(M2="","Has Client Signed Docs","Follow up Settlement Date"))))))) etc etc

Is there any way for me to have tasks displayed to cover the entire process?

I have attached the sheet.

Thanks for your help

Hi

Here's one way.

on sheet 3 row 1, put in the required statements for the relevant columns eg
E1: Follow up Relevant Pages
F1: Follow up Submission
on sheet3 row 2 down, put in a formula to test the respective cell on sheet work eg
E2: =ISBLANK(Work!E2)
F2: =ISBLANK(Work!F2)

On sheet work
B2: =INDEX(Sheet3!$1:$1,,MATCH(TRUE,Sheet3!2:2,0))

This will find the first appearance of a true in sheet3, and find the relevant heading that matches.


For the formula in work!C2, you could build a lookup table with the heading results to be found in B2, a cell reference (e2, f2 etc) and the amount that has to be added (1,2...)

Something like
Follow Up Submission,E2,1
chaise AIP,F2,2

Then you could use a formula similar to

=INDIRECT(VLOOKUP(B2,Sheet3!A4:C5,2,FALSE))+VLOOKUP(B2,Sheet3!A4:C5,3)

to bring back the relevant result. In this example, I've put the lookup table in Sheet3:A4:C5 but that would probably have to be moved...

HTH

rylo

Thanks heaps champ - works beatifully