How To Select Odd Numbers In Excel?

Excel question

• , I found your answer to Yuri's question on 8/24/06 most helpful. It was the formula to read/skip data in one column and post to another. the formula was Indirect("A"&(row()-1)*5+1) could you help me to expand this to select the first four rows of col "A" and copy to col "C" and then skip 20 rows and copy the next 4 consecutive rows etc. Mark I have anspreadsheet I receive daily. With a referral person's name in A2 & A3. In C2, D2 and E2 is a friend's first, last name and email address. In F2, G2, and H2 is another friend's first, last name, and email address. This continues for 8 friends. Everyday I manually cut/paste F2, G2, and H2 into C3, D3 and E3 (and so on for the rest of the 8 people listed). How do I automate this tedious, time consuming process in excel? How can I sum all negetive numbers from a list based on a criteria in adjacent column. eg. gbp -300 cdn 50 gbp 150 cdn 200 gbp -100 This is just a sample list- I want to sum all the negative numbers in column 2 for "gbp" from column 1. Please let me know what formula can be used? what is V look up and H look up and also Pivot Table? How can we use this Pls send me answer of this quistion with example insheet if it possible. Thanks Vinod I had to make a new question because it said I had to many follow up questions. Can you answer me at blakemperdue at gmail dot com ? So in response to your question... Correct, if there is not a sequence number, then there will not be any instance that there is a sequence number on that list. The column will not exist, and that's why it's absent in the A1 column when it's pasted into(EX: A1: R000000000 10.25 2009-07-06 JOHN DOE 111-11-1111 JOHN DOE). So basically I need the exact thing you provided me, but it will not need the sequence number. J1 (amount) and K1 (issue date) will now be I1 and J1. Thank you! I am trying to include the value from the cell above in my active cell, using that value in a formula. I also want to use the A value for the row that I am in. Example: B1 = 5. A2=1,A3=2,A4=3 , so on. I want B2 to = B1*(15/A2). but then I want B3= B2*(15/A3).... and B4= B3*(15/A4).... and so on. How do I make a formula to do this without typing this in for every row? I am using 2003. I am building a sheet that I want to only import mulitiple cell data on one row only if the answer is no. I am running into probles using an if statement. I haveand need to know how to set up a spread sheet using a formula that can help me add entries to all of the points scored by fantasy football players...to everyone in my pool, without having to "plug in" every point from every player to every individual fantasy player! In other words if twenty people play and seventeen of them pick the same football player...how do I add the point for everyone all at once? This is an absolutely non-profit fantasy league. (you can even play if you like) Can you help me? I have created a form in excel. My question is, is there a way to set up a form so that it has a form number (kind of like an invoice number) and when I print, it will print with a continuous progression (1,2,3 and so on) without having to manually enter and print 1 page at a time? I have a salary range that I need to change into a 13 steps with the first step and the 13th step staying the same as the beginning and ending of the range. With the same % between steps. For example $21,000 - $43,000 range converted into a 13 step salary schedule with $21,000 as step 1 and $43,000 as step 13 and each of the steps in between having the same % between steps. Hope this makes sense. Attached is the sheet I am working on. In Line # 11 I have created a list. For and example if Frame is selected in line 11, I need value associated with frame to appear in Cell D19. (if frame = 0.50, if brick = 0.45) I've been trying to solve this problem for quite a while and I just can't seem to figure it out. I'm not familiar with setting up a database but I'll just get to the question. I have a number *10PHY00001* (including the asterisk) and I need it to increase the "00001" section by 1 all the way up to 50000. Eg. *10PHY50000*. Is there anyway to do this? The help is MUCH appreciated! I have a formula inthat uses a fixed rate in one of its parts. But when I drag it to the rest of the row it doesnt understand that I want this cell to be fixed(one cell in the formula)? I have recently gathered the information from statistics towards nfl and mlb players through the past 10 years; i was hoping to find an if then statement or something that would make it possible to gather the statistics over those years (also needing to know if they have to be on the same worksheet or not, they currently are not) say i have 5 players fromand 5 from 2008, 2 of them are the same person i would like to get the combined numbers on another sheet.

• Answer:

  So if each cell in A1 had its cell address such as A1 A2 A3 A4 A5 then in column C C1: A1 C2: A2 C3: A3 C4: A4 C5: A25 C6: A26 C7: A27 C8: A28 C9: A49 C10: A50 etc in C1 put in this formula =INDEX($A:$A,TRUNC((ROW()-1)/4)*24+MOD((ROW()-1),4)+1,1) then drag fill down the column C Hello Rochelle, A macro would work wonders here. Copy from Sub MoveColumn() to End Sub and replace the Range references as appropriate. For example if the Range where the date is being copied from is not A2:B2, then change that to show thereference (i.e., B4:D4). I hope this macro can help you automate your process going forward. Please Remember to change the line of code that reads: Application.Run "Book1!MoveColumn" where Book1 is the filename. Good luck, Mary A. Lindsey eDoc Support LLC www.edocsupport.com Sub MoveColumn() ' ' MoveColumn Macro ' Move Columns to form a data sheet ' ' Keyboard Shortcut: Ctrl+R ' Range("A2:B2").Select Application.Run "Book1!MoveColumn" Range("A2:B2").Select Selection.Copy Range("A3").Select ActiveSheet.Paste Range("A4").Select ActiveSheet.Paste Range("A5").Select ActiveSheet.Paste Range("A6").Select ActiveSheet.Paste Range("A7").Select ActiveSheet.Paste Range("A8").Select ActiveSheet.Paste Range("A9").Select ActiveSheet.Paste Range("A10").Select ActiveSheet.Paste Range("F2:H2").Select Application.CutCopyMode = False Selection.Cut Range("C3").Select ActiveSheet.Paste Range("I2:K2").Select Selection.Cut Range("C4").Select ActiveSheet.Paste Range("L2:N2").Select Selection.Cut Range("C5").Select ActiveSheet.Paste Range("O2:Q2").Select Selection.Cut Range("C6").Select ActiveSheet.Paste Range("R2:T2").Select Selection.Cut Range("C7").Select ActiveSheet.Paste Range("U2:W2").Select Selection.Cut Range("C8").Select ActiveSheet.Paste Range("X2:Z2").Select Selection.Cut Range("C9").Select ActiveSheet.Paste End Sub It's basically a multiple condition sumif so this method will do it for you =SUMPRODUCT(--(A1:A1000="gbp"),--(B1:B1000<0),B1:B1000) replace A1:a1000 and b1:b1000 with the actual ranges you want to test - this does need to use explit ranges, it won't work with A:A, and the minus signs are important - this is just an ordinary function however! Vlookup is a function used to lookup a related value for a given value vertically. For eg. column A contains the code numbers of 100 employees of a company and column B contains the employee names. If you want to find out the name of the employee whose code is 25, then you have to type in the formula in the cell where you want the below formula: =VLOOKUP(25,A1:B100,2,FALSE) Here 25 is the employee code, A1 to B100 is the array where the code and the employee names are given, 2 is given as the names are in the second columni.e.B and FALSE is given because the names can be arranged in any order. Had that been TRUE then the names would have to be mentioned in ascending order. Similar is the case for Hlookup. In this case it is used for horizontally listed names. I am not an expert of pivot table hence I would request you to ask some other expert. You may also try the HELP menu in excel. Open anworksheet and press F1. In the search box type in the desired function and you will see the details (This is how I have learnt). Blake, this should do it: Sub ABC_nosequence() Dim sh1 As Worksheet, sh2 As Worksheet Dim r As Range, s1 As String, s2 As String, s3 As String Dim s4 As String, s5 As String, s6 As String, s7 As String Dim v As Variant, v1 As Variant, v3 As Variant, v4 As Variant Dim i As Long Set sh1 = ActiveSheet sh1.Copy After:=Worksheets(Worksheets.Count) Set sh1 = ActiveSheet Range("A1:A3").Select Do Set r = Selection(1) s1 = r Set r = Selection(2) s2 = r.Text Set r = Selection(3) s3 = Application.Trim(r.Text) v1 = Split(s1, " ") v3 = Split(s3, " ") s4 = Left(v3(UBound(v3)), 2) s5 = Replace(v3(UBound(v3)), s4, "") s5 = Left(s5, 5) s6 = "" ' clear city variable For i = LBound(v3) To UBound(v3) - 1 s6 = s6 & v3(i) & " " Next s6 = Application.Trim(s6) ReDim v(1 To 11) v(1) = v1(5) v(2) = v1(4) v(3) = v1(3) v(4) = Selection(2).Text v(5) = s6 v(6) = s4 v(7) = s5 v(8) = v1(0) v(9) = v1(1) v4 = Split(v1(2), "-") s7 = v4(1) & "/" & v4(2) & "/" & v4(0) v(10) = s7 Selection(1).Resize(1, 10).Value = v Selection(1).Offset(0, 9).NumberFormat = "mm/dd/yyyy" Selection(2).Resize(2, 1).EntireRow.Delete Selection(1).Select Selection.Offset(1, 0).Resize(3, 1).Select If Len(Trim(ActiveCell)) = 0 Then Exit Do Loop End Sub Simple: you type the exact formula you showed here in cell B2 and then copy that cell downwards.will adjust the column and row references accordingly. You can also drag down the fill handle instead of copy/paste (the tiny black square at the bottom-right corner of the selected cell). Tip: If you want a row or column to stay put during a copy, precede it with the dollar sign: =$A$1 will never change =$A1 Only row numbers change =A$1 Only column letters change Not sure I've got enough information to provide an answer, but possibly see http://www.aidanheritage.byethost3.com/excel/Array_Filter.xls which is an array filter MIGHT be what you need - please feel free to ask follow up questions and if it helps my email is [email protected] there are a number of sheets already on the net for fantasy football, but it SOUNDS as though it's a SUMIF - I'm happy to look at any sample sheets if that helps! My email is [email protected] http://www.aidanheritage.byethost3.com/excel/exampleDataValidation.xls explains SumIf as well as a lot of the other functions in excel Not as such but see http://www.aidanheritage.byethost3.com/excel/invoice.xls which uses VBA - in your case the macro would have to be modified to handle the print as you would need a custom print command - it would print once, then increment and so on. You might also like to check the google search http://www.google.co.uk/#hl=en&source=hp&q=excel+invoice+number+increment+on+print&rlz=1R2ACAW_en&aq=f&aqi=&aql=&oq=&gs_rfai=&fp=a3823ce889f6cdd7 Kathy A1: 21000 A2: 43000 B1: =A1 B2: =($A$2-$A$1)/13+B1 then drag fill down to B14 that is one interpretation. a second (probably the one you want) would be D1: =A1 D2: =($A$2-$A$1)/12+D1 then drag fill down to D13 see the attached picture. If one of those isn't what you want, then post back/post a followup. Good day, Hope I will help with this answer: You will have to put the value of each item next to it in the sheet you used to create the drop down list; frame 0.50 Brick 0.45 Then in the cell where you want to D19 type in this formula =VLOOKUP($B$11,List!$A$1:$B$14,2,FALSE) The easiest way I can think of: - remove the trailing asterix from the first one - select that cell and use the fill handle (the tiny square at the bottom-right of the cell selector) and drag down. The number will increase. Next, use an adjacent column with this formula: =A1&"*" Double-click the fill handle to fill down that formula. Now copy that column and paste-special values it on top of the A column. I am not sure that I fully understand the question, but-- If you want the reference in a formula to remain the same then you can put a dollar sign in front of the row or column in the reference and they will remain absolute. I.E. +A1 when dragged down one row will be +A2 (using relative referencing), but +A$1 when dragged down one row will remain +A$1. If you have put a number in a cell such as 10 and you drag it down it may change to 11,12,13,etc. What it is doing is filling the series of numbers by incremental of one. If you cpy it this way--make the 10 the active cell grab the drag handle of the cell, right click drag down and select copy cells the 10 will remain the same and not increment. Richard USA Florida You can use vlookup to find relavant data on something and bring across worksheets. You can use vlookup like this: =vlookup(e2,Sheet2!$a$1:$d$20,3,false) e2 is what you are searching for. Sheet2!$a$1:$c$20 is the range you are searching 3 is the 3rd column in the range. so, here 3 is column C. false means that you want exact match. This formula will look for the value from the 3rd column of the range (columns a through c) for a value in e2. There is good help withinfor vlookup formula. vlookup formula can be copied into multiple cells at one time. Just make sure that the absolutes are used for the range and as needed. Mark