Returning Formatting on a Lookup

I have a named range with values containing special formatting (i.e., 
underlined initial letters, bolding, etc) that I would like to have returned 
to another range based on a lookup(key) value.  The lookup is successful 
(using VLOOKUP) however, the special formatting is not returned.  Both field 
are text fields.  Is there some way to perform a lookup and have the 
formatting returned as well as the value?
0
Utf
2/10/2010 9:23:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
589 Views

Similar Articles

[PageSpeed] 14

You would need a macro to "look back" and copy the format from the source to 
the destination (vlookup() result).
-- 
Gary''s Student - gsnu201001


"Ken Ent" wrote:

> I have a named range with values containing special formatting (i.e., 
> underlined initial letters, bolding, etc) that I would like to have returned 
> to another range based on a lookup(key) value.  The lookup is successful 
> (using VLOOKUP) however, the special formatting is not returned.  Both field 
> are text fields.  Is there some way to perform a lookup and have the 
> formatting returned as well as the value?
0
Utf
2/10/2010 9:37:01 PM
thanks, I was afraid of that.  If I knew how to write a macro to find a 
specific cell and copy it's formatting, I would.  Will look for alternatives, 
but thanks for responding.

"Gary''s Student" wrote:

> You would need a macro to "look back" and copy the format from the source to 
> the destination (vlookup() result).
> -- 
> Gary''s Student - gsnu201001
> 
> 
> "Ken Ent" wrote:
> 
> > I have a named range with values containing special formatting (i.e., 
> > underlined initial letters, bolding, etc) that I would like to have returned 
> > to another range based on a lookup(key) value.  The lookup is successful 
> > (using VLOOKUP) however, the special formatting is not returned.  Both field 
> > are text fields.  Is there some way to perform a lookup and have the 
> > formatting returned as well as the value?
0
Utf
2/11/2010 1:42:01 PM
Reply:

Similar Artilces:

Word 2004 formatting
For some reason Word now opens my documents in a defined space in the center of my monitor. I can enlarge the image (ie, 150%) but the defined space doesn't change size so I lose text. Changing from Normal, to Page Layout, etc doesn't fix the problem. What's wrong and how do I fix it? Many thanks! Jim When you hit the green button in the top left, what happens? When you drag the bottom right corner, what happens? On 18/2/07 1:28 PM, in article 1171765694.927622.224610@t69g2000cwt.googlegroups.com, "jb_powell@qwest.net" <jb_powell@qwest.net> wrote: > For som...

Multiple Match and return header
I am trying to find a way to Match on multiple values and return the header a row based on that. I have tried looking at combinations of vlookup, match, and index and so far haven't been able to figure out anything that can do what I need. I have a list of data with a person's name in the first column, and then headers for an action they took. Then data in each cell is then the date range they took the action. here is an example: Action 1 Action 2 Action 3 Action 4 John 2/3/2005 2/5/2006 5/7/2006 9/25/2006 Bob 1/3/2006 5/3/200...

Captions and Citations won't format differently on same line.
Sir/Ma'am, Here's my issue: I have several figures in my document, which I have captioned using the "caption" command. Additionally, I have given the source for the caption as well. For instance: "Figure 1: Elm Tree (Jones, 2007)." I have created a Table of Figures, and the table of figures views the citation as part of the caption. When I try to select the citation and change it to normal text style (so the table of figures won't recognize it as captioned text) it will not let me change it if it is on the same line. My style book requ...

leading zeros in text format
I receive bulk orders via an Excel spreadsheet, containing many individual orders with reference numbers. The reference numbers may be 7-9 digits long, however the field needs to have 10 digits. The format is set to customize: 0000000000. In this manner, we always see the 10 digits regardless of the actual number. The problem we have is that we bulk-load this spreadsheet through a specific custome built bulk-loader. This reference number field must have 10 actual digits, not format digits. Therefore a number such as 00865243 becomes 865243 and then will not run through the bulk loa...

Criteria to return all records if selection from form is null
Hi, I'm trying to pass multiple query criteria from form controls. I'm using the following type of syntax for the criteria: Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*") And this works fine for the fields where there are no Nulls in the data, but if there are Nulls in this field I don't get those records. Quite understandable since Like "*" doesn't return Nulls. What I'm trying to do is to return all records when there is nothing chosen in the selection box on the form. I've tried a bunch of different IIfs wi...

International date formats
When performing an Edit, replace on the cell value 10.09.03 (a system genereated date vale of 10th Sep 03) replaceing the . with /, Excel (and in VBA when recorded as a macro) sometimes changes the cell value to 09/10/03 (9th October 03). This is driving me potty and have spent a vast amount of time on it trying to work out what Excel is doing!!!! My Regional settings are set fro the UK. Any ideas anyone? "Mike" <michael.may@npower.com> wrote in message news:2d44701c39483$97f77730$a601280a@phx.gbl... > When performing an Edit, replace on the cell value > 10.09.0...

Excel 2007: Return rownumber when text data on 3 columns match exa
Using: Excel 2007 I want to document Windows Group Policy settings side by side on a single sheet. To accomplish this task I want to return the row number of the current sheet where the data of the row that has an exact match. The data I want to lookup is in three colums. Example: Column B | Column C | Column D ------------------------------------------------------------------------------------------- Policy | Control Panel | Load a specific visual style Policy | Desktop | Do not add shares Policy | Desktop | Hide Network Location...

Lookup data in the list
Hi All, I have a list as shown bellow, I want to pick up the date correcponding to the value on cell A1 and the value in B2 or B3. So it is some thing like this =Vlookup(a2,a:b[b2+1] or[b+2] {this is just a vierd example} ColumnA ColumnB ID Name 1 12082A IN-tuition 2 Ms. Gayle 3 Gayle Theresa 4 Sofiane 5 12082B tuition 6 Ms. Gayle 7 12298A Language Institute 8 Senouci Bereksi 9 AOUL 10 Bereksi Kindly Help ASAP, As I am Stuck in my assignment. Hi, Manish. The better way, of course, is to have the ID in col A, the company name in B, the individual name in C a...

Conditional Formatting #28
Hi everybody I use Excel 2002 sp2 Is there a means to have more than 3 conditions for confitional formatting ? the dialog box only features 3. many thanks and best regards Jean Luc J.E. McGimpsey shows a way to get up to 6 different formats using conditional formatting and a custom number format. You can't add more conditions, but you may be able to use a worksheet event that formats the cell the way you want. Jean Luc wrote: > > Hi everybody > > I use Excel 2002 sp2 > Is there a means to have more than 3 conditions for > confitional formatting ? the dialog box ...

Cell Formatting to disp. ### numbers
I am trying to format the cells so that it only allows three numbers t be displayed. To be more descriptive: We work with zip codes quite often, but, we only use the first thre digits. Someone sent us a xls file with 12000 zip codes, in one column, and need to know how to make the column show only the first three digits o all the zip codes.. there is another problem, when I convert them to a numeric value, i removes the zero in front...ex. 08245, becomes 8245, but i need to kee that zero in front. Thanks -Eri -- Message posted from http://www.ExcelForum.com Assuming your zip codes a...

Reprint AP check format on plain paper
Provide the ability to print a "copy" of a previously printed check w/ stub (with "void" in the signature section) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defaul...

Pivot Chart Refresh cancels all chart formatting
Hello folks, I am using pivot chart with calculated fields, on which I did some quite sophisticated formatting (combination chart stacked bar / dot plot with secondary axis ; bar & plot area & grid colors ; labels ; axis & chart titles). When I click the "refresh" (!) button to update the data, the chart formatting is lost and the chart reverts to the default formatting (stacked bar chart with all series) Is there any way to "memorize" the chart formatting on a pivot chart ? TIA -- NZ Loss of formatting is a known problem with pivot charts. There's inf...

Excel default number format problem
I have a user with an incorrect default cell format in Excel. His default number of decimal places for a cell with numbers is set to 4 instead of 2. I have no idea how he caused this and I have no idea how to correct it. In case I'm being ambiguous, here is the procedure: Open a new Excel workbook, choose any cell and go to Format. In the Format Cell dialog box go to the Number tab and choose the Number category. The Decimal Places box will be set to 2 by default but in this user's Excel it is set to 4. How do I get it back to the default of 2? http://www.imagedump.com/index.cgi?pick...

formating multiple worksheet via print preview
I have a multiple worksheet workbook that I need to change several features about all the worksheets (page orientation, fit to 1 page, etc.). How can I do this formating and have it apply to all the worksheets selected in the group rather than having to do it to each worksheet separately? Thanks in advance. Roger Roger Group the worksheets you want to change. Change the relevant settings on one of them and it will be reflected in the others. Don't forget to ungroup the sheets afterwards. Andy. "Roger" <anonymous@discussions.microsoft.com> wrote in message news:0...

Can't enter Item Lookup Code
This is a strange one. We've set up a POS, and every now and then it is suddenly impossible to enter anything in the item lookup code field of the pos, or scan an item. You can type anything you want, nothing will appear. I can solve this by logging in as administrator, go into security mode, press Ctrl+S, select the item lookup code column, close the "Security" window (nothing checked there, btw), Ctrl+S again, leave security mode. After that all is normal, for a while. Has anyone seen this before? What is your current version of RMS? Have you tried running a repair on it?...

Matching number formats
I am using an INDEX/MATCH formula and do get a "VALUE NOT AVAILABLE" response. Both ranges have a "General" format. A [Copy][Paste Special][Formats] does not make the formats match. How can I make them respond to each other? On Thu, 12 May 2005 10:36:09 -0700, "raystub" <raystub@discussions.microsoft.com> wrote: >I am using an INDEX/MATCH formula and do get a "VALUE NOT AVAILABLE" >response. Both ranges have a "General" format. A [Copy][Paste >Special][Formats] does not make the formats match. How can I make them >resp...

sort/extract strings with similar format
I have a download of bank statements. The description column contains firms reference number which is in the following format 1numeric,1alpha, 5numeric eg. 9z99999, 1f12345. The problem is that the description column would read: deposit cape town 9z99999 CAMS payment 1f12345 5j54321 Payment form john D 3a98765 Is there any way I can sort or extract the firm number to a seperat column if it is not always in the same place in every row? They are i the same column! I would really appreciate it if anyone could help me solve this +--------------------------------------------------...

format cells depending on value range
Using Excel 2003 and am trying to format cells depending on a range of values e.g. If the value is less than 50.999% then colour the cell red If the value is between 51% and 74.999% then colour the cell orange If the value is more than 75% then colour the cell green I have managed to get the first and third examples to work using conditional formatting (formula is: =B4<0.50999) but can't seem to get it to work if the value is between two specific values. Any help would be appreciated. Maybe =B4<51% for red =AND(B4>=51%,B4<75%) for orange =B4>=75...

Conditional Formatting #10
2 things.... 1. - I have got a list that is linked to a cell - when a paticular item in the list is chosen I want the cell to be filled with a colour - i.e. Conditional Formatting - however how do I allow for more than 3 conditional formats - there are about 7 choices from the list. 2. - Linking from the above - I then want the rest of hte information in that row to be coloured the same as box in problem 1. I cannot seem to get the Conditional Formatting to work for this..? Any ideas..? Thanks Vis. You'll have to use some VBA code to do this. Add this to the worksheet module ...

Lookup value on a table??
I have the following table below. If I place in cell "A100" the figure -7 and in cell "A101" the figure -975. What formula do I need to put in cell "102" the result, in this case it would be 787. Also on column "A" if the figure is between two (ie -960) it should use the row with the highier value, in this case the result would still be 787. A B C D E F 1 -10 -9 -8 -7 -6 2 -1000 790 790 788 786 784 3 -975 791 791 789 787 785 4 -950 791 791 789 787 785 5 -925 79...

item # format changes to Date when data is retrieved in SSMS
This is a multi-part message in MIME format. ------=_NextPart_000_0017_01CA945B.2EF4A440 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable GP 10 SP3 , SQL 2005 SP2 Our Item Number format in GP-Inventory is 00-00-0000. When I run smart = list and export the data to Excel, works fine. But I run a query in SSMS: about 253 items out of 14,000 get listed as = date format; means 01/11/2010 Any idea why is this happening ? ------=_NextPart_000_0017_01CA945B.2EF4A440 Content-Type: text/html; charset="iso-8859-1" Con...

Opening a csv file with US date format on a Australian PC
Having problems with formating dates correctly in Excel when opened from a CSV file. The data coming in is in the US format "MM/DD/YYYY HH:MM AM/PM". IE: "6/17/1985 12:00:00 AM". When the CSV file is opened in Excel you are unable to change the formatting to set it to Australian date format "DD/MM/YYYY HH:MM AM/PM"; the data stays the same as "6/17/1985 12:00:00 AM". When the MM and DD digits are 12 and less it recognises them as valid dates. IE "11/1/1995 12:00:00 AM" is automatically formated as "11/01/1995 0:00". However ...

Unable to format cells
In my Excel 2003 application, I select a range of cells and set a vertical border. This also works in Excel 2007 until I save it from the latter into the compatible format. If I then open it again with 2007, it's inoperative. I select the range, right-click, then click on format cells and nothing happens. If I open it again with 2003, it works. If I then save from 2003 and then open it with 2007, it will work. Any ideas? ...

Lookup / Summary Table
I want Excel to lookup a value in a table and return each row it finds that value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I don’t want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I don’t want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? How about PIVOT table? "Joe" wrote: > I want Excel to lookup ...

Dialog shall not end on RETURN
I have a dialog based application with an Button that simply ends the application. The is selected (tab-wise) after startup of the application. If one then presses ENTER the application quits. If one changes the tab value to another button it does not end. If it is set to the only Edit Control window the application is quit as well. I however do not want the application to be quit by pressing enter, simply because that can be done inside the Edit Control. So how can I change this ? Matthias Matthias Pospiech wrote: > I have a dialog based application with an Button that simply ends ...