Conditional formatting

[Guest]

Hi

The problem I am trying to address is that on my Sheet 1 I want to create a
template for uploading catalogue data. As such the fields have to be of
defined length or default entries. I am pasting the raw supplier data in
Sheet 2 and pulling it through into the template.

Descriptions are limited to 40 characters so I have set an IF formula to
strip out the first 40 if the raw data is longer or return the raw data if
less. However, I want to highlight that the description has been truncated
and therefore the user should check how meaningful it is and overtype if
necessary.

Where the text has been truncated, I tried to flag it with a conditional
format checking if the length of the entry on Sheet 2 is >40 but it says it
cannot do this. Because the template has to be in a set format I do not want
to introduce additional columns for flags on Sheet 1. Is there a way to get
around conditional formatting from another sheet or is it possible to
identify that my IF statement has produced a TRUE or FALSE status?

As always I appreciate any help offered.

G

 

[Guest]

Give the first description cell a name (INSERT=>Name=>DEFINE==>Name: e.g.
MyData, Refers to: =Sheet2!$A$1 (if data starts in cell A1)

On your first shhet, highlight the desciption column and set the CF to:

FormulaIs; =LEN(offset(MyData,row()-1,0))>40 and set colour.

Adjust ROW()-1 to suit

HTH

 

[Guest]

Hi Toppers

Thanks for that. It works well. If in fact I need to check the first 4
columns is it possible to modify the formula by adding a column ref and
defining a name to an area rather than column.

Also can you use this method in my original IF statement to strip out the
first 40 characters if Sheet2!A1 contains more than 40?

Thanks

 

[Guest]

Try this:

Highlight columns and enter CF:


=LEN(OFFSET(MyData,ROW()-1,COLUMN()-1))>40

and think you need to retain the IF statement.

HTH

 

[Guest]

Hi Toppers

With reference to the IF statement Has I have now created a named range for
the data to use in the CF statement it would be useful to use names in the IF
statement. I have
=IF(LEN('Sheet2'!A1)>40,LEFT('Sheet2'!A1,40),IF(LEN('Sheet2'!A1)=0,"
",'Sheet2'!A1)) which I of course copy down. However the raw data from
another supplier might not be in column A so if I could use the same range
name in my IF statement as for the CF statement I would not have to keep
editing the IF statement formula.

G

 

[Guest]

Try:

=IF(LEN(OFFSET(Mydata,ROW()-1,0))>40,LEFT(OFFSET(Mydata,ROW()-1,0),40),IF(LEN(OFFSET(Mydata,ROW()-1,0))=0," ",OFFSET(Mydata,ROW()-1,0)))

 

[Guest]

Another option:

Create named variable "MyFormula" as '=OFFSET(Mydata,ROW()-1,0)' (no
quotes) and then:

=IF(LEN(MyFormula)>40,LEFT(MyFormula,40),IF(LEN(MyFormula)=0," ",MyFormula))

Or

=IF(LEN(MyFormula)=0," ",LEFT(MyFormula,MAX(40,LEN(MyFormula))))

HTH

 

[Guest]

Hi Toppers

That is excellent. Thanks for all your help on this.

 

[Guest]

Hi Toppers

I don't know if this is still flagging to you and I am sorry to keep picking
your brains.
As I said the formular works really well but I have come across another case
I would like to enact. If the formula returns #NAME? because the user has
not identified any data to pass to the template (each supplier may not
provide all the information) I want it to default to a blank. I tried adding
an ISERROR but it returned 0.

My formula (in A2) was =IF(ISERROR(A2),"
",IF(LEN(MyFormula)>40,LEFT(MyFormula,40),IF(LEN(MyFormula)=0," ",MyFormula)))

Can you see what's wrong or suggest an alternative approach?

Thanks

 

[Guest]

I think the #NAME is likely to be because one of the named ranges i.e.
'Mydata' or 'MyFormula' in my examples, in the formula is incorrect; absence
of data (as it is text) should (wiil) not cause this error.

If the problem persists send sample workbook to:

toppers at REMOVETHISjohntopley.fsnet.co.uk