A few months ago I had asked a question and got this answer: Question: I have a a set of values in A1 through A100. I need to look up each value and find a match in another set of values located in C1 through C200. If a match is found then I need the formula located in column B to return the value in the same row but the next column over (D). Answer: =IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)), VLOOKUP(A1,$C$1:$D$200,2,0), "") Copy down through A100. The formula worked fine, however the reference value and the look up value are not any more numbers but a combination of letters and numbers (i.e. T-410TFG2-A) with a general format and the formula is not reading them. How can I fix the formula? Thanks in advance for your help. Regards, ZUO

0 |

5/16/2010 11:05:01 PM

Just use the Vlookup part: =VLOOKUP(A1,$C$1:$D$200,2) or maybe this with error checking: =IF(ISNA(VLOOKUP(A1,$C$1:$D$200,2)),"",VLOOKUP(A1,$C$1:$D$200,2)) Regards, Per On 17 Maj, 01:05, Zuo <Z...@discussions.microsoft.com> wrote: > A few months ago I had asked a question and got this answer: > > Question: > I have a a set of values in A1 through A100. > I need to look up each value and find a match in > another set of values located in C1 through C200. If a match is found then I > need the formula located > in column B to return the value in the same row but > the next column over (D). > > Answer: > =IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)), > VLOOKUP(A1,$C$1:$D$200,2,0), "") > > Copy down through A100. > > The formula worked fine, however the reference value and the look up value > are not any more numbers but a combination of letters and numbers (i.e. > T-410TFG2-A) with a general format and the formula is not reading them. How > can I fix the formula? > > Thanks in advance for your help. > > Regards, > > ZUO

0 |

5/17/2010 12:02:10 AM

Hi All by a sudden all incomming mail with text format are attached as a text file. I don't remember me doing anything.. Other mail like html is not attached as file. Anyone seen the same ? Setup: Outlook 2000 (only popmail) on windows 2000 and McAfee Enterpise 7.0 Best Regards /martin Uppsala Sweden ...

Hi. I have a quick question. I have a workbook that makes new worksheets and renames the tabs based on a value in a column in sheet 1. I got this code from Ron here on this discussion group and it works great. He pointed me to http://www.rondebruin.nl/copy5.htm and I used the "create new sheets from unique values" code. What I would like to do is after these new sheets are created, apply a consistent formatting to all of them. The formatting is extensive and I currently have it on one worksheet named "FormatForSheets". I would love to be able to apply this form...

I've wracked my brains for the formula that allows me to sum the days between an entered date (A1) and the current date (E1). The "current date" in the formula would be entered automatically into the E cell as part of the formula. In other words, suppose A1 was 5/1/06 and today's date is 5/6/06. E1 would know that 5/6/06 is today's date without my having to enter it, and the formula would give me 5 days as the answer. Thanks!!!! Try =TODAY()-A1 format as genera -- daddylongleg ----------------------------------------------------------------------- daddylon...

Does anyone know any barcode scanner and label printer solutions that integrate with the Great Plains fixed asset module? ...

Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for eac...

Hi all, Our shop uses MSPS which is integrated with MOSS. If I set a task to Fixed Units (a % of total resource utilization across all PM's), then the resource updates their time in MOSS and then I accept the time against the schedule, the resources utilization on the task moves from the original % designation. Shouldn't the other variables change and leave the Units fixed, as designated? Also, using server is there a way to set the resource allocation at the project level instead of the task level? When I go to enter at the project level I get a message saying someth...

I need to create a formula that looks at cell P6 and if <> 0 does a calculation, if P6 is equal to 0 it moves to cell Q6 and looks to see if it is 0 or not, if not then it creates a calculation then onto cell R6 etc. The calculation needs to be based on whatever the last cell is that is <>0. P6 is the last cell <>0 then use that otherwise move to the next cell. The formula needs to be applicable up to cell W6. Thanks in advance! Ok, so if a cell <>0 does that mean it contains some number other than 0? If so, are these numbers always positive? -- Bif...

I'm pretty new to the Excel VBA and need help with reading values in a cell. I keep getting some kind of global error whenever I use the Value property. How should the following code read? 'loop through all of the records on ConvertToFile and copy each row to the appropriate spreadsheet Worksheets("FileToConvert").Activate Range("A5").Select Do While Not Range(ActiveCell).Value = "" Select Case Range(ActiveCell).Offset(0, 12).Value End Select Range(ActiveCell).Offset(1,0).Select 'move down to the next row Loo...

Would like to be able to run a report that includes either the "Fixed Amount", and or "Maximum Commission" fields. We use these features and would like to be able to change items that have amounts in these fields but need to be able to run a report to find what items have these fields populated. ...

Hi A cell (H6) in my spreadhseet currently contains the following formula: =IF(ISBLANK(G6),"",NETWORKDAYS(E6,G6)) If the value returned is >1 then i want the font in the cell to turn red. Please help. Thanks Dan If you are using Excel 2007 you can use conditional formatting. It should show up on the "Home" banner. If you're not in 2007, not sure that it can be done. "housinglad" wrote: > Hi > > A cell (H6) in my spreadhseet currently contains the following formula: > > =IF(ISBLANK(G6),"",NETWO...

This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C550AF.9525D2B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am fairly new at using Excel - I am attempting to create a spreadsheet = that includes vehicle information.... the Vehicle Title Numbers are = seventeen digits - after I insert those numbers they appear to be = calculated/converted in some way - the seventeen digit number is = converted into the following: 5.74094E+15=20 If I double-click within that cell, then the number reverts back to the = sevent...

This is a multi-part message in MIME format. ------=_NextPart_000_0007_01CA9617.8A358D20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Vista Basic x32 sp2 Has anyone figured out yet how to prevent mobsync.exe form periodically = starting and trying to sync even though one has all syncronizinging off?? So far the only way I've found is to deny access to mobsynv.exe but I = now get a DistributedCOM event error (10001). James ------=_NextPart_000_0007_01CA9617.8A358D20 Content-Type: text/html; charset="is...

I write a commentary on excel which relates to figures in the same page. I want to cut down on my time so I want to use "&" formula's to link into the cells e.g. 'sales are up "&cell&" on last year'. The formula's work but I can't convert that number into a % of £ sign or reduce the decimal places. Any ideas? ="Sales are up by "&TEXT(C5,"0.0%") or ="Sales are up by "&TEXT(C5,"�#,##0.00") -- HTH RP (remove nothere from the email address if mailing direct) "Luke" <...

I need help. I have this formula: =3DIF(ISERROR(VLOOKUP(IF(C3=3D"",VLOOKUP(D3,Data!B:O,F3+2,0),IF (D3=3D"",VLOOKUP(C3,Data!B:O,F3+2,0))),$O$3:$O $114,1,0)),H3&"C0MISCELLANEOUS",H3&(VLOOKUP(IF(C3=3D"",VLOOKUP(D3,Data! B:O,F3+2,0),IF(D3=3D"",VLOOKUP(C3,Data!B:O,F3+2,0))),$O$3:$O$114,1,0))) And I tried using this code to run instead of the formula above because the formula takes about 10 minute to run for worksheets, but it gives me #Value! at row 100. Sub Run_Data() Dim iLastRow As Long Dim i As Long With Sheets("FBL3N_1&quo...

corpse_make_list={{[magic_ring];1;1;12.0603};{[stem];1;1;3.9799}} This is the data in the cell, for a game. The last two numbers 12.0603 and 3.9799 are what I need to change, they vary throughout the column. Is there a way i can make a formula for this to change each end number by a certain amount, say 10X what it is now? Thanks for any help, Im very new to Excel :D Zemo, This should show you how to do it: Goto Cell A1 and type exactly this ="corpse_make_list={{[magic_ring];1;1;"&B1&"}; {[stem];1;1;"&C1&"}}" Now in B1 put in 12.0603 and...

Using Excel 2003: I have a complicated quote template in Excel that does everything I need at present. This has grown from simple beginnings and I think may now have gone beyond the capabilities of Excel but the bosses want to stick with Excel. My problem now is nested If formulas. I currently have the following formula to look up a price against a part number in one of 5 separate price list files. --------------------------------------------------------------- =IF(ISBLANK(B26)=TRUE,"",IF(which_price=2,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices...

Hi, Must have clicked on a function key - do not know - but the whole sheet is now a grey colour. I have tried highlighting the whole page and format/ cell/ patterns and changing to 'no colour' but cannot get it to go back. Help Mike Maybe you hid all rows or columns? CTRL + a(twice in 2003) to select all cells the Format>Row(or column)>Unhide. Gord Dibben MS Excel MVP On Wed, 10 Sep 2008 18:06:01 -0700, MikeR-Oz <MikeROz@discussions.microsoft.com> wrote: >Hi, Must have clicked on a function key - do not know - but the whole sheet >is now a grey colour. I...

Hi All How to check the cell is text or number ? When Cell is string, Case Excel close and recoveing. Sub FormatNum2str() '~~ 2010/02/18 Dim iRows As Long Dim iCols As Long Dim ir, ic As Long Dim iNum As Long Dim strVal As String If Application.Selection Is Nothing Then MsgBox "No Open Worksheet", vbCritical Exit Sub End If iRows = Application.Selection.Rows.Count iCols = Application.Selection.Columns.Count For ic = 1 To iCols For ir = 1 To iRows With Application.Selection.Item(ir, ic) If .NumberFormatLocal = "Gene...

I am currently using MATCH formulas to pull certain data from SHEET1 and place the appropriate data in various cells in SHEET2. My problem is that now I want to keep SHEET2 as it is but also to Save As SHEET2 to a different location and for it to stop updating the information in it when I make changes to SHEET1. I basically want SHEET2 to be an independant spreadsheet after I pull the appropriate information from SHEET1. So is there a way to remove the many formulas that are in the cells of SHEET2 and just keep the text of the results in those cells? Is this possible? --- Message post...

I would like to pass a variable through a function. Based on the value, I have some ranges already name ie. Dataquestion#. I would like to be able to change the named range to use in this formula based on numbers, such as mQNo. Any help would be appreciated. mQNo = 1 Set mQuestionRange1 = Worksheets("Data").Range(Chr(34) & "DataQuestion" & mQNo & Chr(34)) This is siomple. I don't think you understadn strings. You don't need a second set of double quotes. It is that simple for example Var1 = "abc" Var2 = "def&q...

I have a problem when I try to open Altavista, google, Msn and others. There is a page named: www.thebestSE.com that always take place and it does not allow me to visit other pages. How can a resolve this? This newsgroup is devoted to Microsoft Access, the database development product. Your question should probably be directed to the group: microsoft.public.security -- Cheryl Fischer, MVP Microsoft Access Law/Sys Associates, Houston, TX "Perdo" <perdo3117@bellsouth.net> wrote in message news:#bR6EPeGEHA.1396@TK2MSFTNGP11.phx.gbl... > I have a problem when I try to...

I need to write a formula in excel that say's: IF E17 = 5 Then C24 should be C24/2 Else C24. Currently C24 is a calculation for material cost, IF a particular option is picked I want to cut that cost in half, if the option is not picked leave the cost alone. Does anyone have a suggestion as to how to do this. I assume I should put this formula in a cell other than C24 since C24 is already a calculated field. --- Message posted from http://www.ExcelForum.com/ Hi A cell can have either a value or a formula. So in another cell, say D24: =IF(E17=5,C24/2,C24) -- HTH. Best wishes Harald ...

I'm running MS Office XP Std My formula's in Excel are not working properly. I've done a check for updates with office on my pc. how do I fix this? -- Dave Hi Dave, > My formula's in Excel are not working properly In what way are your formulae not working? --- Regards, Norman "Dave" <Dave@discussions.microsoft.com> wrote in message news:FEC8DCA1-D3D7-4CDB-B3D4-AB78DA940B62@microsoft.com... > I'm running MS Office XP Std > My formula's in Excel are not working properly. I've done a check for > updates with office on my pc. ho...

Hello, I'm using the Conditional Formatting tool on the Home>Styles ribbon to format unique cells. I'd then like to copy and paste the formatted cells so that there the conditional operation is no longer running on them. For example, I have values in column A and B. The two columns independently have only unique values, but have duplicate values between them. I want to highlight the unique values and copy only column B (along with the highlighting) to a new workbook. Does anyone know how to do this? "eggman2001" <sodani@gmail.com> wrote in message news:e63a68a4-f...

on SHEET1 A1 I HAVE A FORMULA =SUMPRODUCT(--(B1:B9999=X) on SHEET2 A1 I HAVE A ,--(C1:C9999=O)) HOW CAN I LINK THIS TWO SHEET, IF CHANGE THE DATA ON SHEET2 A1 LIKE THIS>>> ,--(C1:C9999=P)) THE FORMULA OF SHEET1 A1 WILL BE >>>> =SUMPRODUCT(--(B1:B9999=X),--(C1:C9999=P)) TNX IN ADVANCE GOD SPEE -- xtrmhype ----------------------------------------------------------------------- xtrmhyper's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2385 View this thread: http://www.excelforum.com/showthread.php?threadid=38151 I don't think y...