Excel Function Question??

[akemeny]

Hi,

I have a somewhat complicated question regarding how to apply a function
under certain circumstances. Here's the situation:

I have a macro set up to color code the rows based on the information placed
in column A. However, I would like to eliminate the need to actually enter
anything into column A by placing a function (or Macro if possible) into the
rows of column A that will enter certain information when something is
entered into another cell.

For example:

Cell A6 is what we call a Status Column... all it tells us is what level of
appeal the claim is in. Cell A6 is also the cell that the macro corresponds
to in regards to our color coding. Cells W6, AE6, AK6, & AS6 are where we
get our information for the Status Column.

I need a function that will allow me to only have to enter information in
cells W6-AS6 that will automatically place the correct Status in the status
column (at which point the macro will kick in and update the coloring on the
spreadsheet).

Thanks

 

[Bob Phillips]

What are the rules for determining the status?

 

[Ron Rosenfeld]

Hi,

I have a somewhat complicated question regarding how to apply a function
under certain circumstances. Here's the situation:

I have a macro set up to color code the rows based on the information placed
in column A. However, I would like to eliminate the need to actually enter
anything into column A by placing a function (or Macro if possible) into the
rows of column A that will enter certain information when something is
entered into another cell.

For example:

Cell A6 is what we call a Status Column... all it tells us is what level of
appeal the claim is in. Cell A6 is also the cell that the macro corresponds
to in regards to our color coding. Cells W6, AE6, AK6, & AS6 are where we
get our information for the Status Column.

I need a function that will allow me to only have to enter information in
cells W6-AS6 that will automatically place the correct Status in the status
column (at which point the macro will kick in and update the coloring on the
spreadsheet).

Thanks

Just use conditional formatting based on the contents of column A.

If you have more conditions than allowed by your version of Excel (pre-2007: 3;
2007: many), you could use an event-triggered macro to do something similar.
--ron

 

[akemeny]

Um.. I'm not sure what rules you mean. But we determine the status based on
the those columns below. If we place reject in those columns the status will
change to one of the following:
RAC, FI, QIC, ALJ

If we place accept in those columns the status will change to: Closed

 

[Bob Phillips]

You are talking in very vague, general terms, whereas computers want
absolutes.

I am asking you to tell us what value in Cells W6, AE6, AK6, & AS6 will
determine what goes into the status column. Phrases like ... columns the
status will change to one of the following: RAC, FI, QIC, ALJ ... do not
clarify anything.

 

[akemeny]

Ok... heres the detailed version then.

The column titles are:

A6 = Claim Status
W6 = Accept / Reject
AE6 = Accept / Reject RAC Decision
AK6 = Accept / Reject FI Decision
AS6 = Accept / Reject QIC Decision

When:

W6 = Reject; A6 = RAC
AE6 = Reject; A6 = FI
AK6 = Reject; A6 = QIC
AS6 = Reject; A6 = ALJ

Currently the only one we can get to work is W6. The problem that we are
having is overriding the previous reject.

For example: We rejected the decision at the first level (W6) therefore the
claim status is RAC. We have sent out an Appeal to the next level (AE6), it
was upheld and we have rejected that decision... so now the Claim Status
should read FI (and so on).

What we don't want to have to do is manually change the Claim Status. We
would like it to automatically change to the correct level of Appeal based on
when we reject the previous decision.

 

[Bob Phillips]

Is this what you want?

=IF(AS6="Reject","ALJ",IF(AK6="Reject","QIC",IF(AE6="Reject","FI",IF(W6="Reject","RAC",""))))

 

[akemeny]

That works perfectly. Is there a way to add more elements to it though? I
also need it to include (in the same format) what the status would say if we
placed Accept in each of those cells. Whenever we placed Accept in any of
those cells it would all generate the same result "Closed". Is that possible
or would it contain to many factors for the function?

 

[Bob Phillips]

What would happen if AS6 said Accept and W6 said Reject (and vice versa)?

 

[akemeny]

Because of the way our spreadsheets work if W6 says Accept then there would
be no data entered in any of the following cells. So we could end up having
the following:

Row 1: W6=Reject; AE6=Reject; AK6=Accept
Row 2: W6=Accept
Row 3: W6=Reject; AE6=Reject; AK6=Reject; AS6=Accept
Row 4: W6=Reject; AE6=Accept

But we wouldn't have any instances where:
Row 1: W6=Accept; AE6=Reject

Everything in our spreadsheet has a trickle effect. Each one of those
columns has two options either Reject or Accept. If we Accept at any level
then thats where that ends.. there won't be any data after that.

 

[Bob Phillips]

I think that this works

=IF(OR(AS6="Accept",AK6="Accept",AE6="Accept",W6="Accept"),"Closed",IF(AS6="Reject","ALJ",IF(AK6="Reject","QIC",IF(AE6="Reject","FI",IF(W6="Reject","RAC","")))))

 

[akemeny]

Perfect!!! Thanks for all your help

I think that this works

=IF(OR(AS6="Accept",AK6="Accept",AE6="Accept",W6="Accept"),"Closed",IF(AS6="Reject","ALJ",IF(AK6="Reject","QIC",IF(AE6="Reject","FI",IF(W6="Reject","RAC","")))))

--
__________________________________
HTH

Bob