Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to get the text format when referencing
Let me explain my issue with an example.
Lets say I have a link to a website , for example http://www.microsoft.com/ in cell A1 which is formatted in a specific font and color and is also a hyperlink. When I want to have the same information in cell A1 in other cells say C3 and B5, I use =A1 in the cells C3 and B5. But what happens is that only the information http://www.microsoft.com is copied to cells C3 and B5 and not the format of the text or the hyperlink. How can I have the format also to be copied to cells C3 and B5 when referencing. Thanks in advance. Vamshee |
#2
|
|||
|
|||
A function cannot change the formatting of any cell including itself.
You can gain color formatting and borders by using Conditional Formatting, but you are not copying formatting from another cell. You would need to use a macro, or copy manually. To copy only the formatting manually Copy the source selection to paste only the formatting Edit, Paste Special, paste formats. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Vamshee" wrote in message ... Let me explain my issue with an example. Lets say I have a link to a website , for example http://www.microsoft.com/ in cell A1 which is formatted in a specific font and color and is also a hyperlink. When I want to have the same information in cell A1 in other cells say C3 and B5, I use =A1 in the cells C3 and B5. But what happens is that only the information http://www.microsoft.com is copied to cells C3 and B5 and not the format of the text or the hyperlink. How can I have the format also to be copied to cells C3 and B5 when referencing. Thanks in advance. Vamshee |
#3
|
|||
|
|||
I have given you a simple example. My actual work involves gathering
information from multiple spreadsheets in the same excel document using vlookup. In the example I have mentioned before and the vlookup, I only get the data and not the format info of the cell. Considering the amount of data that is interlinked, I dont want to do it manually. I mean copying the format information manually is not a feasible solution. I looking for a function that will not only copy the data but also the format. Thank you for your help. Vamshee "David McRitchie" wrote: A function cannot change the formatting of any cell including itself. You can gain color formatting and borders by using Conditional Formatting, but you are not copying formatting from another cell. You would need to use a macro, or copy manually. To copy only the formatting manually Copy the source selection to paste only the formatting Edit, Paste Special, paste formats. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Vamshee" wrote in message ... Let me explain my issue with an example. Lets say I have a link to a website , for example http://www.microsoft.com/ in cell A1 which is formatted in a specific font and color and is also a hyperlink. When I want to have the same information in cell A1 in other cells say C3 and B5, I use =A1 in the cells C3 and B5. But what happens is that only the information http://www.microsoft.com is copied to cells C3 and B5 and not the format of the text or the hyperlink. How can I have the format also to be copied to cells C3 and B5 when referencing. Thanks in advance. Vamshee |
#4
|
|||
|
|||
You cannot do that with a function (formula).
A function cannot change the formatting of any cell including itself. If you only have three colors you might be able to do something with Conditional Formatting based on value. You can write a macro to color a cell based on such things as value, or relative position to another cell with the color you want, or something that would be able to do manually. If all you have is an assignment to another cell you could run a macro to look at the formula of a cell within a selection and copy the format from the other cell. Here are a couple of macros that you can try on a copy of your workbook. that appear to work for simple assignment statements. ** In this case, I expect feedback, whether it works or not ** I will be adding these to my colors.htm page. Instructions for installing and using macros in http://www.mvps.org/dmcritchie/excel/getstarted.htm The first macro will only copy the only the interior formatting (pattern color). The second will copy all formatting. Sub ColorOfAssignment() Selection.Interior.ColorIndex = xlAutomatic 'clear color Dim rng As Range, cell As Range Set rng = Selection For Each cell In Intersect(rng, _ rng.SpecialCells(xlFormulas)) On Error Resume Next cell.Interior.ColorIndex = _ Range(Mid(cell.Formula, 2)).Interior.ColorIndex On Error GoTo 0 Next cell End Sub Sub FormatOfAssignment() Dim rng As Range, cell As Range Set rng = Selection For Each cell In Intersect(rng, _ rng.SpecialCells(xlFormulas)) On Error GoTo passby Range(Mid(cell.Formula, 2)).Copy cell.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False passby: On Error GoTo 0 Next cell End Sub testing: ---------------- ONLY tested for within the SAME worksheet. A1: 'A1-1 (pattern color red) A2: 'A2-1 (pattern color blue) A3: 'A3-1 (pattern color yellow) B1: =A3 B2: =A1 B3: =A2 also tested for =Sheet4!A18 ='Sheet four'!A18 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Vamshee" wrote in message ... I have given you a simple example. My actual work involves gathering information from multiple spreadsheets in the same excel document using vlookup. In the example I have mentioned before and the vlookup, I only get the data and not the format info of the cell. Considering the amount of data that is interlinked, I dont want to do it manually. I mean copying the format information manually is not a feasible solution. I looking for a function that will not only copy the data but also the format. Thank you for your help. Vamshee "David McRitchie" wrote: A function cannot change the formatting of any cell including itself. You can gain color formatting and borders by using Conditional Formatting, but you are not copying formatting from another cell. You would need to use a macro, or copy manually. To copy only the formatting manually Copy the source selection to paste only the formatting Edit, Paste Special, paste formats. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Vamshee" wrote in message ... Let me explain my issue with an example. Lets say I have a link to a website , for example http://www.microsoft.com/ in cell A1 which is formatted in a specific font and color and is also a hyperlink. When I want to have the same information in cell A1 in other cells say C3 and B5, I use =A1 in the cells C3 and B5. But what happens is that only the information http://www.microsoft.com is copied to cells C3 and B5 and not the format of the text or the hyperlink. How can I have the format also to be copied to cells C3 and B5 when referencing. Thanks in advance. Vamshee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
Format Number to Text | Excel Worksheet Functions | |||
Format a cell with numbers and user defined text | Excel Discussion (Misc queries) | |||
how to format numbers stored as text or vice versa to use vlookup | Excel Worksheet Functions | |||
How do i change numbers in text format to number format? | New Users to Excel |