Search results

H_Milli, Welcome to the forums. I too am new to VBA -- perhaps that means that my suggestion will be right on par with your needs?? I've attached a file with a macro that does what you have asked. The code I've written is here: Sub StatusUpdate() Dim i As Integer Dim TotalEntries As...

If you are not counting a range of multiple cells -- and you only want to return a 1 or 0 value, depending on whether A3 includes ":", you could: =--(LEN(SUBSTITUTE(A3,":",))<LEN(A3)) or even =IFERROR(FIND(":",A3)^0,0) without CSE.

Hello, What about this: =SUM(--(LEN(SUBSTITUTE(B1:B4,":",))<LEN(B1:B4))) where B1:B4 is the range in question. Executed as an array, by using Ctrl+Shift+Enter instead of Enter alone. There's probably a shorter way to do this...

How about... =IF(AND($F4=0,C4<E4), E4-C4, IF(AND($F4=0,C4>E4),0,BlackScholes($C4,$E4,0.125%,$F4,2.6%,$D4%,"P",0,0)))

If you still prefer a macro solution, try this: Sub ColumnZ() Dim i As Integer Dim Value_Count As Integer Dim Z_Incr As Integer Dim TargetSheet As Worksheet Set TargetSheet = Worksheets("P_L events up to 1 month") Value_Count = WorksheetFunction.CountA(TargetSheet.Range("K:K")) - 1 Z_Incr =...

Pao, If a formula is satisfactory, I'd use something like this in cell Z6: =IF(MOD(ROW(),2)=1,Z5,IF(K5<0,N(Z5)+1,"")) If a macro is required, I'd have some more questions before I could offer a solution.

Righto. So just to make sure we're on the same page -- how will excel know which of the duplicates to ignore? Do you want to keep the duplicate with the highest time value? Or the one nearest the top of the list? For example -- Nabeel Akhtar Gondal appears on line 30 and line 32. Which...

Hi Nabeel, If the time values you provided are Minutes:Seconds, you can paste this formula in V14: =SUM(IF(ISBLANK(D14:U14),0,TIME(--LEFT(D14:U14,FIND(":",D14:U14)-1),--RIGHT(D14:U14,2),))) If the time values you provided are Hours:Minutes, you can paste this formula in V14...

Nabeel, my friend -- you are quite a handful...:) You are asking for something tricky. I will give you my best solution: 1. Turn off data validation in the yellow cell. 2. Type Please Select in the yellow cell. 3. Turn the data validation in the yellow cell back on. See attached. What...

Nabeel, Putting "cue text" into a blank cell is fairly tricky -- I've tried lots of ways, but never been satisfied. You can do it with VBA, but why don't you just put the Please Select: text in cell J8? -- as you've already done with the prompt in cell D7.

Pao, Would it work to paste the following formula in E4 and drag down? =IF(MOD(ROW(),2)=0,ROUND(C4*2,0)/2,E3)

Zach, The good news is: There IS a way to do MEDIAN( IF() ) !! You just need to know the magic word: "Arrays" Take a look at the attached file. Note the formula in C12 is enclosed in brackets. It's an array formula: ={MEDIAN(IF('Data '!$M$2:$M$219="No",'Data '!$J$2:$J$219))} You...

Then paste this formula in Cell L6: =IF(K7="Half Day","Half Day",J6-G6+1) or =IF(K7="Half Day",K7,J6-G6+1) (And delete the custom formatting rules.) attached.

Nabeel, See attached. Is this what you wanted? Here are the changes I made: 1. Adjusted the formula in the red cell (L6) =IF(K7="Half Day",0.5,J6-G6+1) 2. Applied conditional formatting to the red cell (L6) to show a decimal (0.5) when "Half Day" is selected and a whole number with a...

JD, If you were to number the rows in your list of subcategories, you would find that the first blank row after SubCategory 1 is row 5, the first blank row after SubCategory 2 is row 12, the first blank row after SubCategory 3 is row 16, and so on {0,5,12,16,...} In the formula that Faseeh...

I'm kind of interested in avoiding the IFERROR when possible...Here's another, without using the IFERROR. =IF(ROWS($4:4)<(EOMONTH($A$1,0)-$A$1+1),A3+1,"") Paste in Cell A4 of your table and drag down...

Here it is in the sample file you provided...

JC, Here's my suggestion...I hard coded the date in cell A1, and then put the formula in A2 and dragged down as far as a wanted. =IFERROR(IF(A1+1<=EOMONTH(A1,0),A1+1,""),"") See attached. Is this what you are wanting?

Joy, I've put together a rough dashboard...see attached. You can pick any two dates and the dashboard will compile the information you have requested for the inquiries you received during those dates. Maybe this will be a helpful starting point? Spend a few weeks or months collecting the...