Posts by darkyam

Re: Extract Specific Text From Cell - Macro

From your saying "all entries that do not have the company name separated", it sounds like there are instances in column A where this has already been done and you just need the company name brought over. If I'm understanding that correctly, then try:
=IF(ISERROR(FIND("@",A2)),A2,MID(A2, FIND("@",A2)+1,LEN(A2)-FIND("@",A2)-4))
If you wanted it to work only on when A is not blank, you could change the above to =IF(A2="","",<above formula>), and then just copy it down to the bottom of the sheet. Easiest way to do this is select D2, then Ctrl+Shift+Down, then Ctrl+D.

Re: Extract Specific Text From Cell - Macro

You don't necessarily need a macro to do this. Often, worksheet formulas execute more quickly than macros. Assuming the first email address is in A2, try this formula in B2:
=MID(A2,FIND("@",A2)+1,LEN(A2)-FIND("@",A2)-4)
Note that the 4 I'm subtracting at the end assumes that all of these end in .com, .edu, or some other 4 character ending. If some of these end in .uk or something like that, then a slightly more complex formula that finds and cuts the first period after the @ and everything after can be written.

Re: Concatenate Date with String

=Concatenate(Text(A1,"yyyymmdd-hh:mm:ss"),A2)

Re: Hide Blank Rows Automatically Based On Value In A Column

Thanks a lot for this; I had already come across the first one, but the second one is working. By adding

Application.Calculation = xlCalculationManual

after changing screenupdating to false and changing to back to manual at the end, I was able to get it work much more quickly as well. This is great. Thanks again.

I apologize for having to post something that appears to have been answered so many times before on this forum, but I have spent the last 2+ hours searching this site, a few other sites, and a VBA book I have and trying all the codes I found before throwing up my hands in defeat.
What I have is a sheet where the cells in range A9:A3508 have a formula that evaluates to blank if any of a variety of conditions are not met (date falls outside desired range, does not meet filter criteria, etc.) and a number if these conditions are met. What I am looking for is for all rows in that range where A is blank to be hidden (not deleted), and for this to automatically update every time A changes (meaning that if A goes from blank to a number, that I will need that row to become unhidden again). I have considered just recording the macro and calling the function with a button, but as this is for external users, some of whom may be unfamiliar with Excel, I would rather keep it as clean, simple, and automatic as possible. Thank you in advance for your help and, in the meantime, I will press on with my quest.

Re: Insert Leading Zero For Date Variable

Why not just format the cells to be mm/dd/yy? That will put the leading 0 in automatically for both month and day and does not require a macro.

Re: Left Lookup Of All Sheets

Monica,
You can see this page for ways to speed up any macros: http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

Re: Replace First X Digits

I'm not good at VBA, and I'm sure there's a more efficient way to do this, but why not just copy the statements from With to End With and paste below the End With and change With Columns("L") to With Columns("C")?
Also, Dave's formula is meant to be used as an in-worksheet solution. If you are only going to go through these numbers once, then his is probably the quickest, as you could just drag the formulas down and then copy/paste values over the originals and clear the formula columns. If you use his approach instead, you should probably modify it slightly to =IF(OR(LEFT(A1,2)="04",LEFT(A1,3)="(04"),SUBSTITUTE(A1,"04",614,1),A1) to account for the parenthesis before some of the numbers.

Re: Sum With Multiple Criteria

The quotation marks need to be removed from TSK="1" for the formula to work, even if that column is formatted as text. This might also be the cause of the #VALUE! error. Daddylonglegs, thanks for showing that formula; I didn't know there was a way to get an array formula to ignore text.

Re: Sum With Multiple Criteria

If your formula works as it is, then you can just replace the references with named ranges. The named range can simply be an offset formula. For example, for the range in E, the formula for the named range could be =OFFSET($E$2,0,0,COUNTA(E2:E65536),1). With this formula, the range would always be large enough to count the data and would never include more cells than necessary.
You can see this free training page for help on named ranges. http://www.ozgrid.com/Excel/fr…excel-lesson-23-basic.htm

Re: Calculate Totals In The Last 18 Hours

From the headings you gave, I had the spreadsheet set up so that the date was in A, start time in B, end time in C, and number in D. B and C were just formatted as h:mm (in other words, no date was associated with them in the cell, just as no time was in A). Is the sheet setup differently?

Re: Calculate Totals In The Last 18 Hours

=SUM((((C:C)+(A:A))>=NOW()-1)*(D:D))
Confirm with Ctrl+Shift+Enter.
I tried to get it to work with a Sumif formula, but I am not as familiar with that formula.

Re: Range Used In Indirect For Valdiation List

You don't need VBA to do this. In B3, try this formula: =B2&1.
If you want to use the value in B3 as a cell reference, then use Indirect(B3) in whatever formula you want to use that value.

Re: Concatenate &amp; Paste Visible Cells

I'm not good with VBA and I don't know how to get this to happen automatically when you change a filter, but I got this code to work and you can execute it with Ctrl+Shift+C:

Sub CopyPasteSelectedCells()
'
' CopyPasteSelectedCells Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
    Worksheets("Sheet1").Range("ConcatenateRange").SpecialCells(xlCellTypeVisible).Copy
    Worksheets("Stats").Range("A38").Select
    ActiveSheet.Paste
    Worksheets("Sheet1").Range("D2:D1000").SpecialCells(xlCellTypeVisible).Copy
    Worksheets("Stats").Range("B38").Select
    ActiveSheet.Paste
    
End Sub

This code assumes a few things:
1. Your first sheet is named Sheet1. You will have to change this in the code if it is not.
2. The range of D in Sheet1 is from rows 2:1000. If it is greater than 1000, you can change it or, better yet, make D a named range so it auto updates.
3. You have a column that does the concatenation (it is always quicker to do an action in Excel than in VBA if possible) and this column, from cells 2:end is named "ConcatenateRange".

For anyone else reading this, I would appreciate any corrections or simplifications that could have been made. Thanks.

Re: Concatenate &amp; Paste Visible Cells

Unless you are going to be doing this multiple times, the quickest way is to add =concatenate(B2,C2) in an empty cell in row 2 and copy down. Then, when the filter is applied, highlight that column and hit Alt+; to select just the visible cells. You can then copy and paste into Stats.
Depending on what your filter criteria are, option B, again assuming you'll only be doing this once and that you won't mess up the presentation of data by sorting it, is to sort the data so that the records you want to copy are all together and then just selecting and copying them over.

Re: If And Sum Formula

Try =IF(F11=0,$E$39-SUM(F$10:F11)-SUM((E$10:E11)*(F$10:F11="")),SUM($E$39-H11+J11)), hit Ctrl+Shift+Enter, and copy down.

Re: Calculate Payments Based On Different Interest Rates

See attached. I had to add =Today() under your interest rate table to get this to update itself (whited out cell to hide it).

Re: If And Sum Formula

In H11, enter =SUM(($E$10:E10)*($F$10:F10=""),$F$10:F10) and press Ctrl+Shift+Enter.
Then drag down.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]An even better way would be to just put =IF(F10="",E10,F10)+H10 in H11 and drag that down.

Re: Caculate Number To Reach Target

Quote

At the end of the month , the same formula is used to determine whether my dept achieved the target or not. However, at the end of the month, actual received for the month of December is used instead of the forecasted amount. Assuming the actual received by the company is 15,502, then my dept should have completed 4440 as below:

The formula is based off what you said above, which is that you would be judged off a moving target. For obvious reasons, the formula can't give you an answer of what you need to do to hit both your moving target and your fixed one. Please let me know which is more important to you to know.
To answer your question, it is still accurate because 560-605=-45. -45/25=-1.8. 129+(-1.8)=127.2. It is possible the end number could be negative, but it would mean that you are already over your goal for the month.

Re: Caculate Number To Reach Target

1) The formula is not 129+450-438/28, but rather 129+(450-438)/28. This shortens to 129+(12/128), or just under 129.1.
2) My apologies. In cell G10, change COUNT($B$10:$B$37) to COUNT($B10:$B$37) and drag down.
3) Yes and no. From what you said above, the target is a moving target based on how much comes in during a given day. This formula takes into account the inbound (forecaseted if that day has not occurred yet) and calculates what is necessary to close based on that.

Lastly, once you make the change in 2), you might notice that the sum of G does not equal the moving target. This is because the formula for each day is based on a pace. In other words, with 28 days left, and being only 12 behind schedule, your dept. would only need to close 1 extra item every other day to be on target. If you had this same differential on the last day, you would have to close 12 extra items that day, so column G can never equal the moving target unless you are perfectly on schedule.