Assuming that column A of sheet1 are date/times, and not text, and you are attempting to find how many date/times are more than 16 hours old then you simply want to place the following formula in sheet2!F7: =COUNTIF(sheet1!A:A,”<“&NOW()-0.75) (16 hours is 0.75 of a day.) If the data in column A is text, then it … Read more
Try this =IF(A1<25,”Young”,IF(AND(A1>=25,A1<=65),”Middle Aged”,”Old”)) 0 solved How to define a range of values while writing IF condition in Excel?
Assuming your table is in A1:G8 (with headers in row 1), and that your Fund of choice, e.g. “B”, is in J2, array formula**: =XIRR(INDEX(F:G,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8))))),N(IF(1,{1,2}))),CHOOSE({1,2},INDEX(A:A,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8)))))),TODAY())) Copy down to give similar results for Funds in J3, J4, etc. I tend to prefer this to set-ups involving OFFSET; not only is it briefer (and therefore more efficient), … Read more
If there are no spaces between numbers then you can use following formula. =TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A3,” yrs”,REPT(” “,99)),99)),” “,REPT(” “,99)),99)) 2 solved Excel Formula to extract previous word (towards left) from a specific position
Look for a word of column 1 and then search that word in another column of another excel sheet and use the adjacent information to fill the cell solved Look for a word of column 1 and then search that word in another column of another excel sheet and use the adjacent information to fill … Read more
Here’s how to connect the macro to a button Private Sub Button1_Click() Dim val As Long val = Application.InputBox(Prompt:=”Enter Amount”, Type:=1) Range(“C43”).Value = Range(“C43”).Value + val End Sub 12 solved excel popup button to ask how much money to add to an existing cell with an existing amount [closed]
Something like this should get you started. I have tried to comment it pretty thoroughly so as to explain what is happening in the macro: Sub CopySomeCells() Dim targetSheet As Worksheet ‘destination for the copied cells’ Dim sourceSheet As Worksheet ‘source of data worksheet’ Dim rng As Range ‘range variable for all data’ Dim rngToCopy … Read more
To calculate Years of Increases enter the following formula in Cell L2 =IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>A2:I2=FALSE,1,””)))),0) and to calculate Years of Steady Profits enter below formula in Cell M2 =IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>=A2:I2=FALSE,1,””)))),0) Both the above formulas are array formula so commit by pressing Ctrl+Shift+Enter. Drag/Copy down as required. See image for reference. In case you want this formula to be … Read more
I do this on a daily basis saving the file as the previous days date. I modified what I use for what you are wanting: dim name as string name = Cells(1,2).Value & Cells(2,2).Value ActiveWorkbook.SaveAs() Filename:=name 1 solved Using VBA to save a file in excel 2010 with a different name each time [closed]
Your description is not that detailed, but I took a shot, and from what I understand you might be looking to have something like this: Where you would put a number into B1, and then column C would print out as many statements as requested. Here’s the formula to that: C1=IF(ROW()<=B$1,”Enter load for Span ” … Read more
So, going the VBA route, you’ll need to loop through each row in your tab and test the value to see if it’s a duplicate, if it’s a duplicate then you’ll increase a counter variable and once that counter variable hits 3 you start deleting rows. This is a little complicated if you haven’t worked … Read more
Would it work this way? Sheets(“Weekly Plan”).Select ActiveSheet.Unprotect Sheets(“Template”).Select Rows(“1:21”).Select Selection.Copy Rows(“6:6”).Select Range(“B6”).Activate Selection.Insert Shift:=xlDown Sheets(“Weekly Plan”).Select Range(“K10”).Select Range(“K28:K47”).Select Range(“K47”).Activate Application.CutCopyMode = False Selection.Copy Range(“K7”).Select ActiveSheet.Paste Application.CutCopyMode = False ActiveSheet.Protect solved Why does my macro only work once?