IF Limitation?
 

Because of IF limitations, I need some helps.
I need a macro to update one cell value by comparing 3 ells.valu
against 8 cells.value. (3*8=24 conditions)

In plain english it is as below:

To update the (J10)cell.value,I need to compare B10 with K9:R9, C1
with K9:R9 & D10 with K9:R9.

e.g. If B10=L9,C10=R9 & D10=N9 then J10=284

The text value (284) comes from column
K=1,L=2,M=3,N=4,O=5,P=6,Q=7,R=8

The macro will update the column (J) if columns B:D are not empty.

Thanks
Michae

--
Message posted from http://www.ExcelForum.com

IF Limitation?
 

Try this formula instead in J10:
=MATCH(B10,K9:R9,0) & MATCH(C10,K9:R9,0) & MATCH(D10,K9:R9,0)

Michael168 < wrote:

Because of IF limitations, I need some helps.
I need a macro to update one cell value by comparing 3 ells.value
against 8 cells.value. (3*8=24 conditions)

In plain english it is as below:

To update the (J10)cell.value,I need to compare B10 with K9:R9, C10
with K9:R9 & D10 with K9:R9.

e.g. If B10=L9,C10=R9 & D10=N9 then J10=284

The text value (284) comes from columns
K=1,L=2,M=3,N=4,O=5,P=6,Q=7,R=8

The macro will update the column (J) if columns B:D are not empty.

Thanks
Michael


---
Message posted from http://www.ExcelForum.com/

IF Limitation?
 

=IF(ISNA(MATCH(B10,K9:R9,0)),B10,MATCH(B10,K9:R9,0 )) &
IF(ISNA(MATCH(C10,K9:R9,0)),C10,MATCH(C10,K9:R9,0) ) &
IF(ISNA(MATCH(D10,K9:R9,0)),D10,MATCH(D10,K9:R9,0) )

will prevent an error if b10,c10,d10 are empty or no match is found,

JWolf wrote:

Try this formula instead in J10:
=MATCH(B10,K9:R9,0) & MATCH(C10,K9:R9,0) & MATCH(D10,K9:R9,0)

Michael168 < wrote:

Because of IF limitations, I need some helps.
I need a macro to update one cell value by comparing 3 ells.value
against 8 cells.value. (3*8=24 conditions)

In plain english it is as below:

To update the (J10)cell.value,I need to compare B10 with K9:R9, C10
with K9:R9 & D10 with K9:R9.

e.g. If B10=L9,C10=R9 & D10=N9 then J10=284

The text value (284) comes from columns
K=1,L=2,M=3,N=4,O=5,P=6,Q=7,R=8

The macro will update the column (J) if columns B:D are not empty.

Thanks
Michael


---
Message posted from http://www.ExcelForum.com/

IF Limitation?
 

On Mon, 7 Jun 2004 10:07:29 -0500, Michael168
wrote:

Because of IF limitations, I need some helps.
I need a macro to update one cell value by comparing 3 ells.value
against 8 cells.value. (3*8=24 conditions)

In plain english it is as below:

To update the (J10)cell.value,I need to compare B10 with K9:R9, C10
with K9:R9 & D10 with K9:R9.

e.g. If B10=L9,C10=R9 & D10=N9 then J10=284

The text value (284) comes from columns
K=1,L=2,M=3,N=4,O=5,P=6,Q=7,R=8

The macro will update the column (J) if columns B:D are not empty.

Thanks
Michael


---
Message posted from http://www.ExcelForum.com/

You could do it with a formula. One such formula:

=IF(COUNTA(B10:D10)<3,"",CONCATENATE(
INDIRECT(B10),INDIRECT(C10),INDIRECT(D10)))


--ron