Formula won't automatically adjust values...

[christianbiker]

Evening...

I'm not sure I will be able to explain this properly but here goes! I am using the following formula to merge data from a range in a row (N:X):

=TEXT(N3,"yy-mmm-dd")&" > "&"CVIR: "&O3&", "&P3&(IF(Q3>0,", Moving Violation - YES","")&(IF(R3>0,", OOS x "&R3,"")&(IF(S3>0,", Violations x "&S3,"")&(IF(T3>0,", Admin Issues x "&T3,"")&(IF(U3>0,", Significant Event - YES ","") &(IF(V3>0,", Charges - YES ","") &(IF(W3>0,", Impound - YES ","") &(IF(X3>0,", Plate Removal - YES ","")))))))))

This formula is in column Z on each row from 3:300 and returns same information past the word "Level" for each row however it should only display the relevant data. I have included a couple examples below. The first is what it shows when the data is added (which is wrong) and the 2nd example shows the proper information but I must go through each blank cell in that row and press "Delete" or "F2 + Enter". I can't figure out how to fix the formula. Any help would be appreciated.

Example 1 - WRONG:

Inspection Date

CVIR #

Insp Level

Moving

OOS

Violations

Admin

Significant Event

Charges

Impound

Plate Removal

18-06-05

EA01218770

Level 1

1

18-Jun-05 > CVIR: EA01218770, Level 1, Moving Violation - YES, OOS x , Violations x 1, Admin Issues x , Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES

18-06-13

EA01220800

Level 2

1

18-Jun-13 > CVIR: EA01220800, Level 2, Moving Violation - YES, OOS x , Violations x , Admin Issues x 1, Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES

18-06-18

EA01222080

Level 2

1

Yes

18-Jun-18 > CVIR: EA01222080, Level 2, Moving Violation - YES, OOS x , Violations x , Admin Issues x 1, Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES

18-07-06

EA01225280

Level 2

1

Yes

18-Jul-06 > CVIR: EA01225280, Level 2, Moving Violation - YES, OOS x , Violations x 1, Admin Issues x , Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES

18-08-06

EA01235338

Level 1

1

1

2

Yes

18-Aug-06 > CVIR: EA01235338, Level 1, Moving Violation - YES, OOS x 1, Violations x 2, Admin Issues x , Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES

18-09-24

EA01239438

Level 2

1

Yes

18-Sep-24 > CVIR: EA01239438, Level 2, Moving Violation - YES, OOS x , Violations x , Admin Issues x , Significant Event - YES , Charges - YES , Impound - YES , Plate Removal - YES

Example 2 - CORRECT:

Inspection Date

CVIR #

Insp Level

Moving

OOS

Violations

Admin

Significant Event

Charges

Impound

Plate Removal

18-06-05

EA01218770

Level 1

1

18-Jun-05 > CVIR: EA01218770, Level 1, Violations x 1

18-06-13

EA01220800

Level 2

1

18-Jun-13 > CVIR: EA01220800, Level 2, Admin Issues x 1

18-06-18

EA01222080

Level 2

1

Yes

18-Jun-18 > CVIR: EA01222080, Level 2, Admin Issues x 1, Charges - YES

18-07-06

EA01225280

Level 2

1

Yes

18-Jul-06 > CVIR: EA01225280, Level 2, Violations x 1, Charges - YES

18-08-06

EA01235338

Level 1

1

1

2

Yes

18-Aug-06 > CVIR: EA01235338, Level 1, Moving Violation - YES, OOS x 1, Violations x 2, Charges - YES

18-09-24

EA01239438

Level 2

1

Yes

18-Sep-24 > CVIR: EA01239438, Level 2, Moving Violation - YES, Charges - YES

 

[jmacleary]

Hi there. I suspect that the 'empty' cells are not actually empty, and most likely contain one or more blank spaces or other non-displayable characters. This would cause the behaviour you describe. Depending on where the data is from, you have different options. The obvious one is to ensure that when the data arrives it has truly empty cells. Alternatively, try this (I have assumed all columns except the Charges column will have numbers in):

=TEXT(N3,"yy-mmm-dd")&" > "&"CVIR: "&O3&", "&P3&(IF(ISNUMBER(Q3),", Moving Violation - YES","")&(IF(ISNUMBER(R3),", OOS x "&R3,"")&(IF(ISNUMBER(S3),", Violations x "&S3,"")&(IF(ISNUMBER(T3),", Admin Issues x "&T3,"")&(IF(ISNUMBER(U3),", Significant Event - YES ","") &(IF(V3<>"yes",", Charges - YES ","") &(IF(ISNUMBER(W3),", Impound - YES ","") &(IF(ISNUMBER(X3),", Plate Removal - YES ","")))))))))