Identify external references (in formulas) and color format
Hi,
I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
Try this:
Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
Biff,
Thanks, i think this will work and i have a couple of questions.. UDF? is this User Defined Function? If so, where to i go to set this up? Thanks. Sorry to ask what is probably a simple question..if this is a macro how would i set it up (as i am not familiar with Macros). If it is something else can you let me know? I appreciate you answering my post and i think this will work, just need a little help getting it set up. -- Mike "T. Valko" wrote: Try this: Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
UDF? is this User Defined Function?
Yes If so, where to i go to set this up? Let's make a slight change: Function GetFormula(cell_ref As Range) As String Application.Volatile GetFormula = cell_ref.Formula End Function Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the pane that opens Select the file name then right click Select InsertModule Copy/paste the UDF code into the window that opens Return to Excel: ALT Q Then apply the cf. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Biff, Thanks, i think this will work and i have a couple of questions.. UDF? is this User Defined Function? If so, where to i go to set this up? Thanks. Sorry to ask what is probably a simple question..if this is a macro how would i set it up (as i am not familiar with Macros). If it is something else can you let me know? I appreciate you answering my post and i think this will work, just need a little help getting it set up. -- Mike "T. Valko" wrote: Try this: Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
Bif,
Thanks for your help..I got it to work on a test (one sheet workbook), but can't seem to get it to run for all pages in the workbook. Not sure why as I copied and pasted the info that you provided. Essentially, what i want to do is to format cells in 3 ways: If formula, change font to BLUE If formula is external reference, color cell Orange If cell has a number that is hard coded, color cell Green If cell is blank, leave alone. Is there a way to combine all of this into the module or cf piece. thanks again, -mike -- Mike "T. Valko" wrote: UDF? is this User Defined Function? Yes If so, where to i go to set this up? Let's make a slight change: Function GetFormula(cell_ref As Range) As String Application.Volatile GetFormula = cell_ref.Formula End Function Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the pane that opens Select the file name then right click Select InsertModule Copy/paste the UDF code into the window that opens Return to Excel: ALT Q Then apply the cf. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Biff, Thanks, i think this will work and i have a couple of questions.. UDF? is this User Defined Function? If so, where to i go to set this up? Thanks. Sorry to ask what is probably a simple question..if this is a macro how would i set it up (as i am not familiar with Macros). If it is something else can you let me know? I appreciate you answering my post and i think this will work, just need a little help getting it set up. -- Mike "T. Valko" wrote: Try this: Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
I think i see why this didn't work for the whole workbook and cells..
is it in the conditional formatting, where i reference cell (A1)? =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) if this is the case what to i put in there to make sure all cells are checked..I clicked on the upper left corner of the worksheet so the whole worksheet was selected, then applied this cond format..nothing changed...Also i have this listed as the second condition..I wonder if that is the issue as well. -- Mike "T. Valko" wrote: UDF? is this User Defined Function? Yes If so, where to i go to set this up? Let's make a slight change: Function GetFormula(cell_ref As Range) As String Application.Volatile GetFormula = cell_ref.Formula End Function Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the pane that opens Select the file name then right click Select InsertModule Copy/paste the UDF code into the window that opens Return to Excel: ALT Q Then apply the cf. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Biff, Thanks, i think this will work and i have a couple of questions.. UDF? is this User Defined Function? If so, where to i go to set this up? Thanks. Sorry to ask what is probably a simple question..if this is a macro how would i set it up (as i am not familiar with Macros). If it is something else can you let me know? I appreciate you answering my post and i think this will work, just need a little help getting it set up. -- Mike "T. Valko" wrote: Try this: Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
It works for me regardless of which sheet the formatting is applied to.
Here are the conditional formatting conditions: Condition 1 If formula is external reference, color cell Orange Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Condition 2 If formula, change font to BLUE Formula Is: =LEFT(getformula(A1))="=" Condition 3 If cell has a number that is hard coded, color cell Green Formula Is: =AND(LEFT(getformula(A1))<"=",ISNUMBER(A1)) NB: condition 2 is kind of ambiguous. If you have a TEXT string in a cell that begins with an equal sign this will be evaluated as a FORMULA. The problem is in identifying formulas. Every formula starts with an equal sign but after that it's hard to find another character that is exclusive to a formula. For example, =A1 is a formula and '=A1 is a text string. Both start with an equal sign. You might think that you could look for ( ) which *most* formulas contain but *not all* formulas have ( ) as demonstrated with =A1. If you might have TEXT strings that start with an equal sign I don't how to distinguish those from actual formulas. I'm sure it could be done with a more elaborate UDF but I'm not the best programmer. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Bif, Thanks for your help..I got it to work on a test (one sheet workbook), but can't seem to get it to run for all pages in the workbook. Not sure why as I copied and pasted the info that you provided. Essentially, what i want to do is to format cells in 3 ways: If formula, change font to BLUE If formula is external reference, color cell Orange If cell has a number that is hard coded, color cell Green If cell is blank, leave alone. Is there a way to combine all of this into the module or cf piece. thanks again, -mike -- Mike "T. Valko" wrote: UDF? is this User Defined Function? Yes If so, where to i go to set this up? Let's make a slight change: Function GetFormula(cell_ref As Range) As String Application.Volatile GetFormula = cell_ref.Formula End Function Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the pane that opens Select the file name then right click Select InsertModule Copy/paste the UDF code into the window that opens Return to Excel: ALT Q Then apply the cf. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Biff, Thanks, i think this will work and i have a couple of questions.. UDF? is this User Defined Function? If so, where to i go to set this up? Thanks. Sorry to ask what is probably a simple question..if this is a macro how would i set it up (as i am not familiar with Macros). If it is something else can you let me know? I appreciate you answering my post and i think this will work, just need a little help getting it set up. -- Mike "T. Valko" wrote: Try this: Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
Thank you..this worked.
-- Mike "T. Valko" wrote: It works for me regardless of which sheet the formatting is applied to. Here are the conditional formatting conditions: Condition 1 If formula is external reference, color cell Orange Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Condition 2 If formula, change font to BLUE Formula Is: =LEFT(getformula(A1))="=" Condition 3 If cell has a number that is hard coded, color cell Green Formula Is: =AND(LEFT(getformula(A1))<"=",ISNUMBER(A1)) NB: condition 2 is kind of ambiguous. If you have a TEXT string in a cell that begins with an equal sign this will be evaluated as a FORMULA. The problem is in identifying formulas. Every formula starts with an equal sign but after that it's hard to find another character that is exclusive to a formula. For example, =A1 is a formula and '=A1 is a text string. Both start with an equal sign. You might think that you could look for ( ) which *most* formulas contain but *not all* formulas have ( ) as demonstrated with =A1. If you might have TEXT strings that start with an equal sign I don't how to distinguish those from actual formulas. I'm sure it could be done with a more elaborate UDF but I'm not the best programmer. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Bif, Thanks for your help..I got it to work on a test (one sheet workbook), but can't seem to get it to run for all pages in the workbook. Not sure why as I copied and pasted the info that you provided. Essentially, what i want to do is to format cells in 3 ways: If formula, change font to BLUE If formula is external reference, color cell Orange If cell has a number that is hard coded, color cell Green If cell is blank, leave alone. Is there a way to combine all of this into the module or cf piece. thanks again, -mike -- Mike "T. Valko" wrote: UDF? is this User Defined Function? Yes If so, where to i go to set this up? Let's make a slight change: Function GetFormula(cell_ref As Range) As String Application.Volatile GetFormula = cell_ref.Formula End Function Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the pane that opens Select the file name then right click Select InsertModule Copy/paste the UDF code into the window that opens Return to Excel: ALT Q Then apply the cf. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Biff, Thanks, i think this will work and i have a couple of questions.. UDF? is this User Defined Function? If so, where to i go to set this up? Thanks. Sorry to ask what is probably a simple question..if this is a macro how would i set it up (as i am not familiar with Macros). If it is something else can you let me know? I appreciate you answering my post and i think this will work, just need a little help getting it set up. -- Mike "T. Valko" wrote: Try this: Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Mike C" wrote in message ... Thank you..this worked. -- Mike "T. Valko" wrote: It works for me regardless of which sheet the formatting is applied to. Here are the conditional formatting conditions: Condition 1 If formula is external reference, color cell Orange Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Condition 2 If formula, change font to BLUE Formula Is: =LEFT(getformula(A1))="=" Condition 3 If cell has a number that is hard coded, color cell Green Formula Is: =AND(LEFT(getformula(A1))<"=",ISNUMBER(A1)) NB: condition 2 is kind of ambiguous. If you have a TEXT string in a cell that begins with an equal sign this will be evaluated as a FORMULA. The problem is in identifying formulas. Every formula starts with an equal sign but after that it's hard to find another character that is exclusive to a formula. For example, =A1 is a formula and '=A1 is a text string. Both start with an equal sign. You might think that you could look for ( ) which *most* formulas contain but *not all* formulas have ( ) as demonstrated with =A1. If you might have TEXT strings that start with an equal sign I don't how to distinguish those from actual formulas. I'm sure it could be done with a more elaborate UDF but I'm not the best programmer. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Bif, Thanks for your help..I got it to work on a test (one sheet workbook), but can't seem to get it to run for all pages in the workbook. Not sure why as I copied and pasted the info that you provided. Essentially, what i want to do is to format cells in 3 ways: If formula, change font to BLUE If formula is external reference, color cell Orange If cell has a number that is hard coded, color cell Green If cell is blank, leave alone. Is there a way to combine all of this into the module or cf piece. thanks again, -mike -- Mike "T. Valko" wrote: UDF? is this User Defined Function? Yes If so, where to i go to set this up? Let's make a slight change: Function GetFormula(cell_ref As Range) As String Application.Volatile GetFormula = cell_ref.Formula End Function Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the pane that opens Select the file name then right click Select InsertModule Copy/paste the UDF code into the window that opens Return to Excel: ALT Q Then apply the cf. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Biff, Thanks, i think this will work and i have a couple of questions.. UDF? is this User Defined Function? If so, where to i go to set this up? Thanks. Sorry to ask what is probably a simple question..if this is a macro how would i set it up (as i am not familiar with Macros). If it is something else can you let me know? I appreciate you answering my post and i think this will work, just need a little help getting it set up. -- Mike "T. Valko" wrote: Try this: Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
Identify external references (in formulas) and color format
Does anyone know how to do this in Excel 2007? I need basically the same
formatting, i.e. color 1 for links, color 2 for formulas, and color 3 for hard-keyed Thanks Renee "Mike C" wrote: I think i see why this didn't work for the whole workbook and cells.. is it in the conditional formatting, where i reference cell (A1)? =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) if this is the case what to i put in there to make sure all cells are checked..I clicked on the upper left corner of the worksheet so the whole worksheet was selected, then applied this cond format..nothing changed...Also i have this listed as the second condition..I wonder if that is the issue as well. -- Mike "T. Valko" wrote: UDF? is this User Defined Function? Yes If so, where to i go to set this up? Let's make a slight change: Function GetFormula(cell_ref As Range) As String Application.Volatile GetFormula = cell_ref.Formula End Function Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the pane that opens Select the file name then right click Select InsertModule Copy/paste the UDF code into the window that opens Return to Excel: ALT Q Then apply the cf. -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Biff, Thanks, i think this will work and i have a couple of questions.. UDF? is this User Defined Function? If so, where to i go to set this up? Thanks. Sorry to ask what is probably a simple question..if this is a macro how would i set it up (as i am not familiar with Macros). If it is something else can you let me know? I appreciate you answering my post and i think this will work, just need a little help getting it set up. -- Mike "T. Valko" wrote: Try this: Create this UDF: Function GetFormula(cell_ref As Range) As String GetFormula = cell_ref.Formula End Function Then, the conditional format would use the Formula Is option. Suppose cell A1 contains this formula: =SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5) Select cell A1 Goto FormatConditional Formatting Formula Is: =AND(LEFT(getformula(A1))="=",SEARCH(".xls",getfor mula(A1))) Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "Mike C" wrote in message ... Hi, I have a conditional format which allows me to identify/format all cells that have formulas. In this instance it changes the font to Blue so that every number that is blue, i know is a formula. I want to take it one step further and maybe give the whole cell a color if the formula references an external link, while all internal workbook links would just remain blue font, the external references in formulas would cause the whole cell to be colored (say orange). Any ideas? -- Mike |
All times are GMT +1. The time now is 02:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com