lookup value & formatting

Hi,

Is it possible to undertake a vlookup function or such 
like which not only imports the value that is found, but 
also the format of the value, be it a different font or 
colour instance?

Thanks 

Simon
0
anonymous (74722)
11/10/2003 10:58:04 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
402 Views

Similar Articles

[PageSpeed] 0

Only by using VBA. I suggest the following :-

1. Select cell with value
2. Find the value in the lookup sheet
3. Copy the cell containing the return value.
3. Go back to the original sheet & Paste (with format)

Something like this (you will need to amend as necessary) :-

'-------------------------------------------------------------------
Sub GET_LOOKUPVALUE()
Dim MyVariable
Dim FoundCell As Object
'-------------------------------
MyVariable = ActiveCell.Value
Set FoundCell = Worksheets("Sheet1").Columns(1).Find _
(what:=MyVariable, lookat:=xlWhole)
If FoundCell Is Nothing Then
MsgBox ("not found.")
Else
FoundCell.Offset(0, 1).Copy
ActiveSheet.Paste Destination:=ActiveCell.Offset(0, 1)
End If
End Sub
'--------------------------------------------------------------------


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

0
11/10/2003 1:58:16 PM
try something like this where you are doing a lookup for b

Sub copyitall()
With Range("b7")
..Value = Range("a1:a12").Find("b").Offset(, 1)
..Interior.ColorIndex = Range("a1:a12") _
  .Find("b").Offset(, 1).Interior.ColorIndex
End With
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Simon Topping" <anonymous@discussions.microsoft.com> wrote in message
news:02dc01c3a779$842b56a0$a001280a@phx.gbl...
> Hi,
>
> Is it possible to undertake a vlookup function or such
> like which not only imports the value that is found, but
> also the format of the value, be it a different font or
> colour instance?
>
> Thanks
>
> Simon


0
Don
11/10/2003 2:06:31 PM
Cheers Brian.


>-----Original Message-----
>
>Only by using VBA. I suggest the following :-
>
>1. Select cell with value
>2. Find the value in the lookup sheet
>3. Copy the cell containing the return value.
>3. Go back to the original sheet & Paste (with format)
>
>Something like this (you will need to amend as 
necessary) :-
>
>'---------------------------------------------------------
----------
>Sub GET_LOOKUPVALUE()
>Dim MyVariable
>Dim FoundCell As Object
>'-------------------------------
>MyVariable = ActiveCell.Value
>Set FoundCell = Worksheets("Sheet1").Columns(1).Find _
>(what:=MyVariable, lookat:=xlWhole)
>If FoundCell Is Nothing Then
>MsgBox ("not found.")
>Else
>FoundCell.Offset(0, 1).Copy
>ActiveSheet.Paste Destination:=ActiveCell.Offset(0, 1)
>End If
>End Sub
>'---------------------------------------------------------
-----------
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~View and post usenet messages directly from 
http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
11/10/2003 2:10:00 PM
Reply:

Similar Artilces:

Conditional format #12
Hi. I am trying to set a conditional format. If NETWORKDAYS(A1,TODAY())> 3 I would like the cell to format with red shading. How do is set the formula in the condtional statement? I tried ="NETWORKDAYS(A1,TODAY())>3" , but this doesn't seem to work. Thanks, Mike. Hi Mike! It doesn't work because the NETWORKDAYS function is in another workbook (the Analysis ToolPak add-in). You can use a workaround by putting your condition in a separate cell (eg B1) and then referring to that cell: B1 =NETWORKDAYS(A1,TODAY()) Then your conditional format for A1 becomes:...

Where is the Keep Text Formatting feature located in Word 07
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...

Dlookup minimum value
Hello. I'm trying to use Dlookup to get the minimum date from a table. I was trying to do the following command: mDate=Dlookup(min("dateField"),"tblName") Somehow the code points an error on"Min", saying that "Sub or Function not defined". Is it possible what i'm doing? Is there any other way instead of looping through all the records? Thanks. Luis Try DMin() instead of DLookup() -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

formating color
hi i have a formating question. i have cells with number choices i them. for example the cell looks like this. 100, 105, 123, 041, 514, 455 now i want to go back and select one of these. i wanted to highligh the one that was chosen. but it will not let me. is there a way i ca do this? it will only allow me to highlight the entire cell, not jus parts of it. thank you in advanc -- RAPPE ----------------------------------------------------------------------- RAPPEL's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3624 View this thread: http://www.excelforum.com...

How can I customize the Filter Drop down on the formatting toolbar
I want to clear everything out of it, except the customized filters I have created. Can i do that? Project > Filter by > More Filters, then select each one, Edit, and remove the checkbox for Show in Menu. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I want to clear everything out of it, except the customized filters I > have created. Can i do that? > I've tried that - it doesn't affect that drop down filter list - whether they are checked or not. "Andrew Lavinsky" wrote: > Project > Filter by > Mo...

Assign Picklist value to another Picklist
I have 2 picklist (territorycode and Listado) and i want to add the value selected of territorycode picklist into listado picklist Could be something like this in the Onchange Event... crmForm.territorycode.value=crmForm.CFPListado.value; but didn`t work. Any suggestions? Thanks in advance. Daniel Rodriguez SMS - Argentina Try using the returnValue property, e.g.: crmForm.territorycode.returnValue=crmForm.CFPListado.returnValue; "Daniel Rodriguez" wrote: > I have 2 picklist (territorycode and Listado) and i want to add the value > selected of territorycode ...

T-SQL script to update A/P account value of Vendor Card
Hello: For some reason, I just noticed that a question that I posed over an hour ago still has not posted to the newsgroups. So, I'm going to try to repost, here. I am trying to develop a simple T-SQL script in Fabrikam where I update the A/P account of the vendor card with a different account number from what is presently there. When I try each of the scripts below, SQL returns 0 results. Why? UPDATE PM00200 SET PM00200.PMAPINDX = GL00100.ACTINDX from PM00200 as PM00200 join GL00100 as GL00100 on PM00200.PMAPINDX = GL00100.ACTINDX WHERE PMAPINDX IN( select ACT...

Formatting
I received a pub doc from a third party. It was formatted as a 11x16 size. Once I decreased it to 8.5x11 and view the doc under 'print preview' the entirety of the image did not view. Basically, the image didn't shrink with the size. Any input? Patty wrote: > I received a pub doc from a third party. It was formatted as a 11x16 size. > Once I decreased it to 8.5x11 and view the doc under 'print preview' the > entirety of the image did not view. Basically, the image didn't shrink with > the size. Any input? Publisher doesn't resize the contents o...

Comparing Values In 2 Columns
Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

Formatting multiple object types in output from a function?
I'm stumped on this one, and have searched and searched for a solution or how-to with no luck. Perhaps it just can't be done. I have a function that may or may not return objects of multiple types. I've setup the type and formatting information for each of the individual types in format.ps1xml files, and when only 1 type is returned by the function the formatting works as I would expect. However, if two or more types are returned by the function, only the formatting for the first object type is applied and then the other types are displayed however PoSH deems ap...

Conditional Format #20
I see that I can only use 3 conditional formats. I need a few more than 3 is there any way to do this. Kevin Try this free add-in on Bob Phillips' website: http://www.xldynamic.com/source/xld.CFPlus.Download.html Another possibility (albeit limited) is to combine your conditional formats with custom formatting. See J.E. McGimpsey's site for an example: http://www.mcgimpsey.com/excel/conditional6.html HTH Jason Atlanta, GA >-----Original Message----- >I see that I can only use 3 conditional formats. I need a few more than 3 is >there any way to do this. > >Kev...

value of value of a variable.
I tried searching, but no use! I have a Const NameA = "BLA BLA" I have a variable NameB Value of NameB is NameA. How do i get the text "BLA BLA" from NameB variable Is there anyway to do that? something like,, VALUE(NameB) Thanks & Regards Joe Hi Dim NameB as String NameB =3D NameA in a cell you could have range("A1").Value =3D NameB would now have content "BLA BLA" regards Paul On May 21, 12:35=A0pm, Joe <joe.varghese.j...@gmail.com> wrote: > I tried searching, but no use! > > I have a Const Name...

LOOKUP Function #6
I discovered LOOKUP today and it worked fine until I made changes to the table of data. Now it's returning incorrect figures. Any suggestions? AC Sales wrote: > I discovered LOOKUP today and it worked fine until I made changes to the > table of data. Now it's returning incorrect figures. Any suggestions? --------- For LOOKUP() to work properly the data all has to be sorted into order first. Did you perhaps put an out of sequence value into the table? Bill ...

lookup row number
Hi, can you tell me how to lookup the row number of a name in a list. EG say I have a list of names as below: Andrew Mike Steve Ian Paul I would normally just add a column with the row numbers and then lookup the row number in the second column but is it possible to do without adding the extra column by using the row formula someohow? Thanks Hi Michael Try the below.. =MATCH("Mike",A:A,0) -- Jacob (MVP - Excel) "Michael" wrote: > Hi, > can you tell me how to lookup the row number of a name in a list. > > EG say I hav...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

how can I show repeating values in a chart?
I would like to show modes in the form of a pie chart but am not sure how. For example the number 73 comes up 3 times in a column on my spreadsheet, how can I show that compared to the number 50 which come up 2 times in the sheet? Thanks for the help Hi, You will need to compute those values using formula or a pivot table and then chart the results. Cheers Andy Cindy wrote: > I would like to show modes in the form of a pie chart but am not sure how. > For example the number 73 comes up 3 times in a column on my spreadsheet, how > can I show that compared to the number 50 whi...

Automatically update Value for data label
Hello I am using Excel 2003 SP2, and have some graphs which have the value (data label) for the last month. Each month new data is entered and the data label has to be deleted for the previous month and the data label for the most recent month added (it still uses the same old data - new data is only entered for the most recent month). Is there any way where the data label can automatically update with the most recent months value (as the chart updates itself automatically currently). Any ideas appreciated. Thank you in advance. Regards, Nav ...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...

Table-lookup transformations
Hi, we are doing some table-lookup transformations based on LEFT and INNER joins. Probably around 100 - 50K rows/second or something like that. I have noticed that the lookups are getting slower and slower due to a growing database. The server itself is heavily loaded, CPU and IO-subsystem - which I know is not a good combination with SQL server :| I am looking for a way to increase the table-lookup transformation speed without mixing up things with SSIS.. Are there any good ways of doing table-lookup transformations within SQL server? Would it be a good idea to write s...

PDF format pagination
I have several reports utilizing Landscape layout. I followed the Report Writers Guide from the SDK with the Width at 11 and Height 8.25 or smaller. However, when I export it to the PDF format, the layout shows up Portrait. How could I change this? The export is fine in Excel with the correct margin setup. Thanks. ...

CSV, Milliseconds default formatting of mm:ss.0
When I generate some CSV files I need to include milliseconds. When anyone opens the file in Excel (in my case part of Office 2003, SP2) the datetimes appear with a default formatting of mm:ss.0. This is frustrating and appears unprofessional on my part when clients open the file. They assume I have made some sort of mistake. I have already thought of generating using =DATE(blah)+TIME(blah)+millis/(24*60*60*1000) but I don't like the idea. How can I change the default format so that it would be something more sensible and my clients and I could avoid the drudgery of clicking on the...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...

Export to comma-delimited format doesn't seem to work
I apologize for the elementary question. I have an Excel spreadsheet which is simply a list of email addresses. I want to export them to a format allowing me to paste them into the "To" field of an email. (In case you're concerned, this is not unsolicited bulk commercial email). I've been able to export them to a .csv format, but it doesn't seem to have the commas, which confuses me and my email software. Any help will be much appreciated. -- Patty Ayers | www.WebDevBiz.com Free Articles on the Business of Web Development Web Design Contract, Estimate Request Form...