Conditional Formatting on input value referred to multiple tables

[SyedTabassumAli]

Hi There

My last post was solved and now here is another issue.

I need to Conditional format Input Table Values as per fixed values of two multiple tables.
for example: one Table is for Input and the Other for Output (I and O)

now when i enter I in input table then it will search value from Input Table and match Value if it is A then the cell color will turn Red
if it is B then cell color will turn yellow.

and when i enter O in input table then it will search value from Output Table and match value if it is A then Red and if B then yellow.

there is another option if i enter "I/O" in input table then it well search from both tables input and out put and cell color will turn red if reference value is "A"

do i need to use "IF condition" or do i need to generate multiple function formula for both

as input table is getting values from two different tables.


Example file attached here: MyExample.xlsx

[SyedTabassumAli]

Urgent Help required on this one,

[samba_ravi]

Why C2 is Red, in your attached file in Input Table C3 was "N"

[samba_ravi]

SEE THE ATTACHED FILE
3 Name managers were created

[SyedTabassumAli]

its Because C15 is "M"

O is for Output Range that starts from B15:K24, and I is for Input Range that starts from B3:K13.

[samba_ravi]

its Because C15 is "M"

O is for Output Range that starts from B15:K24, and I is for Input Range that starts from B3:K13.

But you said that in your note "4 cell will turn yellow when Input or both table has "N" Normal"

[SyedTabassumAli]

when input value is I then reference range will be from input, and when input value is O then reference range will be from output, and when input is I/O then reference range will be both from Input and output.

when Input range have M then input cell will turn Red, when reference range have N then input cell will turn yellow.

[samba_ravi]

when input value is I then reference range will be from input, and when input value is O then reference range will be from output, and when input is I/O then reference range will be both from Input and output.

when Input range have M then input cell will turn Red, when reference range have N then input cell will turn yellow.

OK then see the attached file

[SyedTabassumAli]

Thanks!
there is one single thing missing,

when the value of input and output is neither M or N or any other value like T or any text then the Input cell remain white.

yes you have nearly cleared my issue, but still i am not getting white cell when the value is other than N or M

[SyedTabassumAli]

Thanks!
there is one single thing missing,

when the value of input and output is neither M or N or any other value like T or any text then the Input cell remain white.

yes you have nearly cleared my issue, but still i am not getting white cell when the value is other than N or M

[samba_ravi]

By default the cell was white then what is the need for white?

[SyedTabassumAli]

because when i change any value other than N or M in Input table the cell color changes to red as it should remain white.

now there is another problem in it. when i enter value O in c2, and the output table has no value, then it became red, as it should remain white.

[SyedTabassumAli]

i thought it was solved but the issue is still there....

when there are M in all cells of Input data table and you enter I as input then it works fine,
but when you change all M into N then cell colors including pattern gets changed into yellow.

i have tried it with multiple option by entering multiple values... but it is not working as it should be.

[samba_ravi]

ok see the attached file

[Tony Valko]

ok see the attached file

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[samba_ravi]

Sorry "Tony Valko", you are repeatedly appealing to give the answer with in the thread. to the possible extent I am trying to give the answer in the Thread along with attaching the sheet.

[Tony Valko]

When you put the solution in the reply it benefits everyone.

When you put the solution in a file it only benefits those that are able to download the file.

[SyedTabassumAli]

issue is somewhere in define ranges,
when output table has no value then input cell works fine. and when input table has no value then input cell works as per output defined range. and it is happening vise versa.

i think i should define it further so it can be solved properly.


There is 1st Table = Input (for "I" only)
There is 2nd Table = Output (for "O" only)
Both these Tables have only three fixed values ("M", "N" and "U")
Row and Column Titles are same for both tables.

There is a 3rd Table = Entry (for Input, Output or Both)

> if Entry = "I" and input table value = "M" then cell turns red, if entry = "I" and input table value = "N" then cell turns yellow, otherwise it remains white. in this stat it has no concern what value is there in output table.

> if Entry = "O" and output table value = "M" then cell turns red, if entry = "I" and Output table value = "N" then cell turns yellow, otherwise it remains white. in this stat it has no concern what value is there in input table.

> if Entry = "I/O" and input or output table value = "M" then cell turns red, if entry = "I" and input or output table value = "N" then cell turns yellow, otherwise it remains white.
in this stat both values of input and output tables are checked
- if any of the table has "M" then cell turns red, either it is "M/N" against "I/O" or "N/M" against "I/O"
- if any of the Table has "N" then cell turns yellow, in this state "M" is not involved, as "N/U" against "I/O" or "U/N" against "I/O"
- if both values are not M or N then cell will remain white.


i hope i have explained it well to understand the problem.

[SyedTabassumAli]

Dear Tony Valko

Don't worry I will post the resolution too, we are near to complete the issue..

file was necessary as the issue was not able to defined without sample.

[samba_ravi]

issue is somewhere in define ranges,
when output table has no value then input cell works fine. and when input table has no value then input cell works as per output defined range. and it is happening vise versa.

i think i should define it further so it can be solved properly.


There is 1st Table = Input (for "I" only)
There is 2nd Table = Output (for "O" only)
Both these Tables have only three fixed values ("M", "N" and "U")
Row and Column Titles are same for both tables.

There is a 3rd Table = Entry (for Input, Output or Both)

> if Entry = "I" and input table value = "M" then cell turns red, if entry = "I" and input table value = "N" then cell turns yellow, otherwise it remains white. in this stat it has no concern what value is there in output table.

> if Entry = "O" and output table value = "M" then cell turns red, if entry = "I" and Output table value = "N" then cell turns yellow, otherwise it remains white. in this stat it has no concern what value is there in input table.

> if Entry = "I/O" and input or output table value = "M" then cell turns red, if entry = "I" and input or output table value = "N" then cell turns yellow, otherwise it remains white.
in this stat both values of input and output tables are checked
- if any of the table has "M" then cell turns red, either it is "M/N" against "I/O" or "N/M" against "I/O"
- if any of the Table has "N" then cell turns yellow, in this state "M" is not involved, as "N/U" against "I/O" or "U/N" against "I/O"
- if both values are not M or N then cell will remain white.


i hope i have explained it well to understand the problem.

"if Entry = "I/O" and input or output table value = "M" then cell turns red, if entry = "I" and input or output table value = "N" then cell turns yellow, otherwise it remains white. "
In this case Input have M and Output have N then what is the cell color - This is satisfying both the conditions 1) input or output table value = "M" then cell turns red, 2)input or output table value = "N" then cell turns yellow

[SyedTabassumAli]

Tony Valko

Sample.jpg

here is a sample image for your understanding what we are working on

[SyedTabassumAli]

when entry is changed with same values in tables,
it looks like this

Sample2.jpg

[SyedTabassumAli]

Dear nflsales,

what if we apply six separate formats for each value?

for two for each I, O and I/O red and yellow format, will it conflict or will it work?

as I and O have reference of different ranges. but I/O need to combine both ranges. what if we use INDEX for both range names "input & output" to refer I/O separately ?

[SyedTabassumAli]

What if we use Name range for Input Values?

[samba_ravi]

Tell me your result in the following cases

For I
Input is M – What is the color
Input is N – What is the color

For O
Output is M – What is the color
Output is N – What is the color
For I/O
Input is Blank and Output is M – What is the color
Input is Blank and Output is N – What is the color
Input is M and Output is Blank – What is the color
Input is N and Output is Blank – What is the color
Input is M and Output is M – What is the color
Input is M and Output is N – What is the color
Input is N and Output is M – What is the color
Input is N and Output is N – What is the color

[SyedTabassumAli]

if we generate Name Range = InputValue | Cell Range = "I, O, I/O" in single column

it only worked for input table range, we just need to change "M" into "N" for color change

AND(INDEX($B$2:$K$11,MATCH($A15,$A$2:$A$11,0),MATCH(B$14,$B$1:$K$1,0))="M",MATCH(B15,InputValue,0))

how can we modify this with condition or should we get the fix value except named range?

i have put input fixed table and input value table in same sheet to check this and it worked.

issue is it worked for only one range at time

[SyedTabassumAli]

For I
Input is M – Red Color
Input is N – Yellow Color
Input is U - No Color/White

For O
Output is M – Red Color
Output is N – Yellow Color
Output is U - no Color/White

For I/O
Input is Blank and Output is M – Red Color
Input is Blank and Output is N – Yellow Color
Input is Blank and Output is U - no Color/white
Input is M and Output is Blank – Red Color
Input is N and Output is Blank – Yellow Color
Input is U and Output is Blank - no Color/White
Input is M and Output is M – Red Color
Input is M and Output is N – Red Color
Input is M and Output is U - Red Color
Input is N and Output is M – Red Color
Input is N and Output is N – Yellow Color
Input is N and Output is U - Yellow Color
Input is U and Output is U - no Color/White

[samba_ravi]

see the attached file
if it not working please give the details where it is not working

[SyedTabassumAli]

Dear nflsales
Finally it is working fine now
Thankx a lot for the help

[samba_ravi]

You are welcome and thanks for your feedback