I want to do something that confuses me to explain, but I'll give it a go. I need to be able to insert a name from one worksheet onto another, depending on a letter in another column alongside the name. I need to be able to do this for several names in a list. I wonder if this is possible as I have no real idea where to start :) Thanks! -- -Liam

0 |

11/17/2009 11:31:02 PM

>I have no real idea where to start :) Start here: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "liamellis91" <liamellis91@discussions.microsoft.com> wrote in message news:1D601DA0-1820-4B02-92F6-5CE517DFB8A6@microsoft.com... >I want to do something that confuses me to explain, but I'll give it a go. > > I need to be able to insert a name from one worksheet onto another, > depending on a letter in another column alongside the name. I need to be > able to do this for several names in a list. > > I wonder if this is possible as I have no real idea where to start :) > > Thanks! > > -- > -Liam

0 |

11/17/2009 11:36:25 PM

Hi, Read up on the VLOOKUP function in the Help menu -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "liamellis91" <liamellis91@discussions.microsoft.com> wrote in message news:1D601DA0-1820-4B02-92F6-5CE517DFB8A6@microsoft.com... > I want to do something that confuses me to explain, but I'll give it a go. > > I need to be able to insert a name from one worksheet onto another, > depending on a letter in another column alongside the name. I need to be > able to do this for several names in a list. > > I wonder if this is possible as I have no real idea where to start :) > > Thanks! > > -- > -Liam

0 |

11/18/2009 3:22:45 AM

ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

KellyMcG03, Below is what I wrote and I am missing a () I think. =IF(j2>8500,"",(IF(F2<8500, (H2-J2),"",(IF(F2>8500,(8500-J2,"") What have I done wrong? Thanks, C -- cefp ----------------------------------------------------------------------- cefpe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1357 View this thread: http://www.excelforum.com/showthread.php?threadid=26722 hi, =IF(J2>8500,"",IF(F2<8500,H2-J2,IF(F2>8500,8500-J2,0))) >-----Original Message----- > >KellyMcG03, >Below is what I...

When combining text and cell values in a concatenate function, I would like to control the number format in the concatenate cell: comma separator, decimal place, etc. ="this is text " & text(a1,"$000,000.00") & " due on: " & text(b1,"mm/dd/yyyy") R1dgeway wrote: > > When combining text and cell values in a concatenate function, I would like > to control the number format in the concatenate cell: comma separator, > decimal place, etc. -- Dave Peterson ...

OK, maybe that wasn't the best way to ask the question... I need to create this scenario: Question: "Lot Size?" Answered by "X" under "Small"(A3) "Medium"(A4) or "Large"(A5) If (A3) = "X", then (J2)= 500 If (A4) = "X", then (J2) = 600 If (A5) = "X", then (J2) = 700 Obviously, these function will have to recide in the J2 box. I can get it to work individually using the "=IF" function, but how can I insert multiple "=IF" functions into the (J2) box? I have tried using the "=OR"...

Certainly there is some function or combination of functions that solves for identifying the last entry in a column. Any help would be appreciated. Example below. column a row 1 aaa row 2 bbb row 3 ccc row 4 ddd row 5 row 6 I would like a function that would use the array A1:A6 to identify the last entry, that being "ddd" which was found at A4. thanks One way: =LOOKUP(2,1/(A1:A999<>""),A1:A999) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) TiChNi wrote: > > Certainly there i...

microsoft office 2007 xl - how do you validate a cell such that you cannot enter a value in cell 1 if a value has been entered in cell 2? ...

Hello, I have a report where the following function counts the interval of days between two dates. This is one of the actual formulas being used: =DateDiff("d",[to_plm],[from_plm]) Now, I need to adjust this syntex so that only the weekdays, workdays, are counted. Please help! -- phm http://lmgtfy.com/?q=access+work+day+function -- Duane Hookom Microsoft Access MVP "phm" wrote: > Hello, > I have a report where the following function counts the interval of days > between two dates. This is one of the actual formulas being used: &...

can anyone tell me please how do i highlight a cell in a spreadsheet with colour if date overdue And how exactly should XL determine that the date was overdue? Take a look at Conditional Formatting in XL Help. If, for instance, the due date is in A1 and the date is overdue if it's in the past, select A1, choose Format/Conditional Formatting... and use something like: CF1: =A1<TODAY() Format1: <patterns>/<color> In article <3B7B11E7-894B-458A-BDE6-7710E0743D31@microsoft.com>, Harvey <Harvey@discussions.microsoft.com> wrote: > can anyone ...

Hi there I am working in excel and I would like to create like a menu/scroll-down in one cell with different options (like including kind of transport, train, airplane, car, etc...) where you have an arrow on the right side and you can choose one of the options given. Thanks for your help. You can use Data Validation to create dropdown lists. There are instructions in Excel's help, and here: http://www.contextures.com/xlDataVal01.html Francisco wrote: > Hi there > I am working in excel and I would like to create like a menu/scroll-down in > one cell with different op...

I'd like to change the size of a cell independtly of another column o row. Someone told me I need to redifine the cell to unlink other cell in that range with this one. That "someone" is no longer around TIA:confused -- Message posted from http://www.ExcelForum.com Hi AFAIK you can't change a single cell indepently from its row/column. The only thing 'someone' could mean would be the merging of cells (goto 'Format - Cells - Alignment). But I would recommend not to use this feature -- Regards Frank Kabel Frankfurt, Germany > I'd like to change the size ...

I am using an IF logical. If the result is true, I would like to return the SUM of a range or cells. Or at the very least return cell reference. eg =IF(K3<=DATE(2010,1,31),AND(K3>=DATE(2010,1,1))) Since this is a true statement, I would like it to return the SUM of A1:A10 Thank you. =if(and(date(2010,1,1)<=k3,k3<=date(2010,1,31)),sum(a1:a10),"whateveryouwant") Because you're using the complete month, you have other choices, too: =if(text(k3,"yyyymm")="201001",sum(a1:a10),"not in Jan 2010") =if(and(month(k3)=1,year(k3)...

You don't. First unlock , then protect. HTH. Best wishes Harald "onemanhattanrebel" <onemanhattanrebel@discussions.microsoft.com> skrev i melding news:84D8A94E-9816-46D6-831F-D66E0B8ADE06@microsoft.com... > ...

If any cell in column D:D contains desired data in string format ( i.e. "Bill G" ), how would I highlight the data range in that respective row? Example: cell d4 contains the text "Bill G". Excel automatically colors the range 4a:4i in red. 1. Select columns A to I. 2. Go to Format > Conditional Formatting 3. Select "Formula Is" on the drop-down arrow, and put: =$D1="Bill G" 4. Press the Format radio button and format as desired. HTH Jason Atlanta, GA >-----Original Message----- >If any cell in column D:D contains desired data in ...

in cell H3, I have a match formula that searches my spreadsheet for a string of information. when it finds the row with the matching string it displays the row number in cell H3 example '63'. In cell C3, I want to display the full text that displays in the row specified in cell H3 '63'. I can do that by simply entering =r63 in the cell C3. I want the info to display, what I would like to do is to automate this so I do not manually have to enter =R63,for each cell. example of my formulas in Cell H3 the formula is =MATCH(I2,(Q$1:Q$30001),0) the result is 63 meaning row 63....

I have a many rows of 12 cells where it is possible to write a number/ text in the cells. I would like to be able to calculate how many of these cells, in one row, that are different from empty, and then multiplie this with the value 8,3 Like if 7 cells in one row are different from empty, then =SUM(7x8,3) how can I do this ? =counta(A1:L1)*8,3 "SpookiePower" <boxjunk2600@gmail.com> wrote in message news:034f545f-b4b1-49ea-a26f-f5cb258a2a82@a16g2000vbr.googlegroups.com... >I have a many rows of 12 cells where it is possible to write a number/ > text...

Is there a short cut way to go to the 1) first row first column 2) last row, last column 3) last row, first column 4) first row last column thanks jery ranch 1 - ctrl/home 2 - ctrl/end 3 - ctrl/end, then home (not ctrl/home) 4 - ctrl/end, then right arrow, ctrl/up arrow, left arrow "Jerome Ranch" <ranchjp@mchsi.com> wrote in message news:n2kgr0ld5b9bt6rguseeubt96ig8pdko3q@4ax.com... > Is there a short cut way to go to the > > 1) first row first column > 2) last row, last column > 3) last row, first column > 4) first row last column > > thanks > je...

Hello all I need to display a msgbox if a value is not found in a table. Something like: If value not_in table.field then msgbix end if I know that code wont work is just an ilustration of what i am looking for Thanks in advance Use DLookup() to see if the value is in the table. If it's not found, the result will be Null. So, use IsNull() to test the result. Here's how to get your Dlookup() expression working: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access us...

IS it possible to scan a list and if criteria is met insert a row that contains a simple summation? Ex: Our company runs a manhour report and once converted to excel it will not give daily totals of manhours just a long list. There will be numerous individuals for one day and then it starts over for the next day but they are not seperated. Can I Search the date and insert the "summation row" at the end of the day. Manually this takes for ever. Please help... Thanx, Nick Hi If you sort them by date you could use Data / Subtotals instead. Another option is to use a pivot...

I am producing a cashflow. It is over five years, I have established that average contract values for 2006, 2007, 2008 2009 and 2010 will be 800k, 1200k, 2000k, 2500k and 3000k respectively. I want the cell with the contract value to change automatically if a project start date is entered, eg if a project starts in March 2007 its value will be 1200k. Can anyone help me? Here are some ideas: a) =CHOOSE(A10-2000,1200,1500,1600........) b)=IF(A11=2006,2000,IF(A11=2007,2000,IF(A11=2008,2500,0))) come back with specific question best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme re...

How do I write the visual basic code for the following. If the value in the cells in column A (sheet 1) matches the value in column A (sheet 2), copy contents of cells N, O & P, columns in the same row, to sheet 2 in A,B & C. Many thanks, in anticipation. Hi, I didn't quite follow your logic A (sheet 2), copy contents of cells N, O & P, columns in the same row, to > sheet 2 in A,B & C. This would overwrite the value in column A which I assume you don't want to do so instead this writes to columns B,C & D in sheet 2. This should work as worksheet code or...

Hi, I have a column for each month where the dates are entered on rows. Is it possible I can color a cell if the date entered in a cell for that month is 15 days past the end of the month. Like, if in a January column I enter a date on one of the rows as Feb.20, then the cell fills with red colour. I tried doing it using conditional formatting, but don't know how to say 15 days past end of the month? Any help will be appreciated! Thanks very much! Shivam What version of Excel are you using? -- Biff Microsoft Excel MVP "Shivam.Shah" <shivams22@gmail.com> wrot...

Hi, I wish to find specific information within the following string of numbers that is always different. Here is an example of what I am looking for: Where to look: *M|AA|YI|EA|EA|HE|IY|KJ|OH|P2|Q4|R<|T4|ZH|Z8|Z9|2U|7P|8(|9+ What I wish to find: If there is an occurrence of "OH" then send to a new tab titled "OH" Also just for fun, if there is an occurrence of T4 send to new tab titled "T4" and if there is an occurrence of YI send to new tab titled "YI" Look at Find and Search in help, that should get you going. -- --- HTH Bob (there&#...

Hi, This is URGENT. Problem: In MS Excel, Column A contains values from A to Z and Column contains value from 1 to 5. Now, I need to count the records that hav "A" has value in Column A and "3" in Column B. I had tried with COUNTI function but it works only for one criteria, i.e. it can be used if want to count the records with value "A" in Column A or the record with "5 in Column B. Is there any function that can be used for thi purpose. Pls treat this very urgent ----------------------------------------------- ~~ Message posted from http://www.ExcelTip...

I am using zz as my variable what I want to do is if zz is between 7 and 10 then print out a range within a sheet is the print range line correct ???????? any assistance appreciated If ZZ = >1 or < =7 Then ' check value of zz Range("A2:G36").PrintOut.SelectedSheets ' Print selected range If ZZ > 8 or < 14Then Range("A2:G36").PrintOut.SelectedSheets You will need to use AND and get the operators in the correct syntax: If zz >=1 And zz <= 7 Then etc which means if zz is between 1 and 7 (inclusive) then do something ... ...