Capturing Data and If formula (Nested)

[Kookai]

Hi Guys,

am not really good in excel and would like to ask help.... might be easy for u guys....

I have 2 work sheets... (request & Report).

In Request Sheet, I've got column

B1 - Reference
C2 - Date Received
I1 - Status (Pending, awaitng Reply,cancelled,Complete,Under study or blank)
J1 - Reply Date
N1-NOC status (Cancelled,Released,Blank)

Now what i want to happen is, in column I1, there is 5 days review cycle after receiving the request. So, if the reviewer, didnt give it after 5 days after received, I1 = Pending , then if its less than 5 days I1 = Under Study. However, if my N1 is RELEASED, I1 = Complete, and if N1 is Cancelled I1 = Cancelled and if N1 is blank my I1 = Awaiting Response, then lastly if C2 is blank, I1 = blank. Is this possible?

Then on Report Sheet,
i want to automatically copy B1,C2,J1 of Request sheet on Report Sheet if its complete, and for other 5 statuses....

I have attached a sample excel... thak you very much for all your kind help...

[NBVC]

Does this work?

=IF(N3="Released","Complete",IF(N3="Cancelled","Cancelled",IF(J3="",IF(C3<=TODAY()-5,"Awaiting Response","Pending"),"Under Study")))

Note, your first value in I3 is "Awaiting Response", yet you have a date in column J, Reply to Originator... is that correct?

You also have a couple of strings in column N with trailing space that needs cleaning up...

[Marcol]

Have a look at this approach.using a Table/List named "RequestTable", and some named ranges.

Select from the drop-down in Sheet "report" C2 to change status catagories.

Change, using the drop-downs in Sheet "requests" Column I, to see the results change.

One possible advantage, other than the standard benefts of a Table/List,is that if you need to add to the result table, just refer to the required table header in row 2 result.
e.g. In F2

Please Login or Register  to view this content.

Then Drag the formula in the adjacent column row 3 across then down (in this case E3)

Hope this is of some interest.