Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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 "Constantly Amazed" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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 "Constantly Amazed" wrote: 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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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(LE N('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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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))) "Constantly Amazed" wrote: 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(LE N('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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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(My Formula)=0," ",MyFormula)) Or =IF(LEN(MyFormula)=0," ",LEFT(MyFormula,MAX(40,LEN(MyFormula)))) HTH "Toppers" wrote: 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))) "Constantly Amazed" wrote: 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(LE N('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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
Hi Toppers
That is excellent. Thanks for all your help on this. "Toppers" wrote: 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(My Formula)=0," ",MyFormula)) Or =IF(LEN(MyFormula)=0," ",LEFT(MyFormula,MAX(40,LEN(MyFormula)))) HTH "Toppers" wrote: 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))) "Constantly Amazed" wrote: 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(LE N('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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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(M yFormula)=0," ",MyFormula))) Can you see what's wrong or suggest an alternative approach? Thanks "Toppers" wrote: 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(My Formula)=0," ",MyFormula)) Or =IF(LEN(MyFormula)=0," ",LEFT(MyFormula,MAX(40,LEN(MyFormula)))) HTH "Toppers" wrote: 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))) "Constantly Amazed" wrote: 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(LE N('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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
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 "Constantly Amazed" wrote: 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(M yFormula)=0," ",MyFormula))) Can you see what's wrong or suggest an alternative approach? Thanks "Toppers" wrote: 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(My Formula)=0," ",MyFormula)) Or =IF(LEN(MyFormula)=0," ",LEFT(MyFormula,MAX(40,LEN(MyFormula)))) HTH "Toppers" wrote: 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))) "Constantly Amazed" wrote: 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(LE N('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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 "Toppers" wrote: 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 "Constantly Amazed" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Conditional Formatting | Excel Discussion (Misc queries) |