Stacked if

[Shocked]

need to "idiot proof" a cell which displays the current status of a complaint.

the following are possible: in lab for evaluation, waiting for material from customer, lab eval complete-sent to admin for disposition, and closed.

my simple log is layed out as such:

a b c d ...
date open date closed customer then y/n/pending/na in each of the following columns going to the right.

if a date open is blank then I'll need an if(isblank(a10),"") to start off with.

should be easy, but for some reason, I just can't put it together today.

for clarification:

open closed customer issue QCeval ra-issued ra-rec'd FinalQC admin status
10/26/09 acme anvil drops prematurely y y y y closed
10/27/09 acme holes only work for roadrunner y y pending waiting on customer

need status to change according to progression thru process: initial QC-> request for product for eval -> waiting on customer -> final eval -> hand off to admin for approval -> approval.

[jj72uk]

Post an example sheet and i will take a look

[Shocked]

example attached.

[ChemistB]

It's late in the day and I'm having trouble figuring out where your "y"'s would be and what you would want for each "y" entered. I think in your Status Column, you can use an equation like this

Please Login or Register  to view this content.

The CHOOSE function matches up an index number (supplied by the COUNTIF function) and returns the appropriate response. For example, if there are no y's in D2:F2, then COUNTIF..+1 will return a 1 and the first response will be chosen which is "Waiting QA". Is this something you can work with?

Edit 2: Create something that's "idiot proof" and they'll just build a smarter idiot.

[Shocked]

it is late in the day and I'll probably have think about this overnight but yes. I think that would work because the process is sequential.

the y's just represent that, yes, this step is complete. (they lined up on MY screen when I typed them-note the sarcasm)

[Shocked]

oooh, just thought of something. Sometimes material is not required to be returned so I would have "na" instead of "y" in that cell/column or the customer can't produce any evidence and we just cancel the whole thing so only initial QC = "y".

I know, late in the day...

[ChemistB]

In those cases where you putting NA instead of "y" does that move along the responses the same as a "y" would? So, an NA and a y would be the equivalent to 2 y's? If so,

Please Login or Register  to view this content.

or if you're on Excel 2007, you can use COUNTIFS instead of COUNTIF.

[ChemistB]

Also, to help idiot proof it, in those cells where you should only be entering y or NA, use Data Validation >List >NA,y to force them to use only those values.

[Shocked]

great ideas, but what if "I'm" the idiot.....?

[ChemistB]

I often idiot proof against myself.