Make it mark "V" if found mark "X"

[muhammad susanto]

hi all..

how to make formula to determine mark "V" (checklist) if in a range e.g range D3:I6 found mark "x"
my "x" in always in different position cell (random)
like this layout :
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]given[/TH]
[TH]given[/TH]
[TH]given[/TH]
[TH]given[/TH]
[TH]given[/TH]
[TH]given[/TH]
[/TR]
[TR]
[TD]x[/TD]
[TD]test1[/TD]
[TD][/TD]
[TD]test5[/TD]
[TD][/TD]
[TD]test9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]test2[/TD]
[TD][/TD]
[TD]test6[/TD]
[TD][/TD]
[TD]test10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]test3[/TD]
[TD][/TD]
[TD]test7[/TD]
[TD][/TD]
[TD]test11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]test4[/TD]
[TD][/TD]
[TD]test8[/TD]
[TD][/TD]
[TD]test12[/TD]
[/TR]
</tbody>[/TABLE]


any help, greatly appreciated

.sst

 

[Yongle]

You could use a helper column

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] formula in J3 copied down[/td][td][/td][td][/td][td] formula in N2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]given[/td][td]given[/td][td]given[/td][td]given[/td][td]given[/td][td]given[/td][td]HELPER[/td][td][/td][td]my text[/td][td]my text is found[/td][td] =IF(SUM(J:J)>0,L2&" is found","not found")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]my text[/td][td]test1[/td][td][/td][td]test5[/td][td][/td][td]test9[/td][td]

1​

[/td][td] =COUNTIF(D3:I3,L2)[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td]test2[/td][td][/td][td]test6[/td][td][/td][td]test10[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td]test3[/td][td][/td][td]test7[/td][td][/td][td]test11[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td]test4[/td][td][/td][td]test8[/td][td][/td][td]test12[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] formula in J3 copied down[/td][td][/td][td][/td][td] formula in N2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]given[/td][td]given[/td][td]given[/td][td]given[/td][td]given[/td][td]given[/td][td]HELPER[/td][td][/td][td]this text[/td][td]not found[/td][td] =IF(SUM(J:J)>0,L2&" is found","not found")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]my text[/td][td]test1[/td][td][/td][td]test5[/td][td][/td][td]test9[/td][td]

0​

[/td][td] =COUNTIF(D3:I3,L2)[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td]test2[/td][td][/td][td]test6[/td][td][/td][td]test10[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td]test3[/td][td][/td][td]test7[/td][td][/td][td]test11[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td]test4[/td][td][/td][td]test8[/td][td][/td][td]test12[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

 

[muhammad susanto]

sorry, i missing it...
i try like this =if(range,="X";then change with "V")

note:
range e.g. D3:I6 not in single cell

 

[Yongle]

You can place a formula in cell D3 and say
IF "X" is found in E3:I6, then D3 is "Y", else D3 is "X")

BUT you cannot REPLACE the value in a cell in the way you want - the ONLY way to do that would be to use VBA

 

[muhammad susanto]

hi Yongle, thank your advice...