Posts by JF

  • Repeat Data As User Fills In Other Cells

    Re: Repeat Data In Column When User Fills In Another


    If the repeated Data is "Static" thne you could use an [if]*[/if] to do what you want.



    =if($b1<>"",1,"")



    You could use the same formula in both col D & E. Just copy down however many rows you want. The when someth9ng is placed in Col B, it will populate D & E with the set value

  • Paste To First Empty Row

    Re: Paste To First Empty Row


    Try something like this:


    Add the below to your code

    Code
    LastRow=range("General!A65536").end(XlUp).row+1


    Then change the destination line to

    Code
    Destination:=Sheets("General").Range("A" & LastRow)


    See if that works

  • Choosing Values From One Sheet

    Re: Choosing Values From One Sheet


    Quote from ByTheCringe2

    Nice bit of rearranging, JF. I did a similar rota thingy for a restaurant on here, but this looked difficult.


    Two things; one, your use of named ranges makes the formulas much easier to understand - I really MUST read up on them!


    Two, a small error: row 2 has row 3 addresses.


    Yeah, sorry about that, originally was working on row 3 with the original data and forgot to change it when I copied the formula


    Actually once it was set for the first day, everything else was pretty much just copy and past then adjust the named range.

  • Postcode Web Lookup And Reply

    Re: Postcode Web Lookup And Reply



    if the value is in A1 try the following:


    Code
    =MID(A1,FIND("( ",A1,1)+2,LEN(A1)-(FIND("( ",A1,1)+4))

  • Rates Simulator: Update Cell Every x Seconds

    Re: Rates Simulator Or Equivalent Function Needed



    You could try something like this:


    If the rates are in Column A (A1-A10 for example) and you want the current rate in C1 then try:


    In the This Workbook module:

    Code
    Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:01:00"), "test"
End Sub


    The in the standard module put:



    This will initiate the timer when the workbook opens and then update the rate in c1 every 3 seconds. When it hits the last rate, it resets the counter back to the beginning.

  • Avoiding Average Of An Average: Times

    Re: Avoiding Average Of An Average




    The N(c1) is a function that converts non-number value to a number, dates to serial numbers, true to 1, or anythin else to 0


    the right & ,8 are the function to take the right most 8 characters of the referance cell. In this case the referenced cell is a concatinateion of the text "00:00:00" and the contents of c1 (:07) so it will take the 8 right most characters of "00:00:00:07" or "00:00:07"

  • Sorting Data Is Changing Formulas

    Re: Sorting Data Is Changing Formulas



    No, at present, it is counting the number of times the team name has been entered in the range (b5:b30).


    There are a couple ways around this.
    1. first, do not enter the names until the game is played (of course then you would need a seperate sheet for "Scheduled" games).


    2. simply, change the formula to total the number Won, lost, Draw


    3. You could use the sumproduct() formula to only count games where there is a score.



    Some other info: Rather than use a static range, I would recommend using dynamiclly named ranges. This will allow for adding more teams if needed. But you should not use blank lines between the teams in this case. By using dynamic named ranges, you would not need to adjust the formulas if teams were added.


    I also just noticed a flaw - at present you were count losses as a score/value of less than 5. Well if there is no score (not yet played) it interprets the null as <5 so counts it as a loss so the formula will need tweeking)


    ----- Added Comment ----


    OK, I tweaked the formula to not count games where there is a blank for the score. FYI, I had to be able to look for a 0 as one of your lines had a value of 0 ) I also added a sheet that is using Dynamic ranges. Try adding a game at the bottom of the list with 2 new team names and give it scores, then adde the New Team Names to the Stats area (Once you do, the rst of the Stats will become visible)

  • Sorting Data Is Changing Formulas

    Re: Sorting Data Is Changing Formulas


    Take a look at the attachment. I used the sumproduct() formula.
    I left youor original section alone and created a new section (g13-o15) with the new formulas.


    I also then copied and pasted/special values below that so you can verify the results after sorting.


    A couple things I noticed, your totals for Nipsters did not add up to 16 games played (8, 2, 3 is only 13).


    Also, No points for ties? If so then your formula for column I needs to be adjusted to add the tie points as well.

  • Sort Cell Data Seperated By Pipe Separator (|)

    Re: Sort Cell Data Speparated By &quot;|&quot;


    Well, using the macro recorder I got the below code. I started with your
    example (P442-C|P642-C|P342-C|P842-C) in A1. Coppied it to D1 and then did a text to column, copied and pasted special/transpose to d3, sorted,
    then reconstructed in d8 and copied and pasted special/value to A2


    You should be able to modify to loop through your sheet or copy to different location


  • Find Combination Of Cell Values Equaling A Specified Total

    Re: Is There A Feature That Finds A Combination Of Cell Values Equaling A Specified Total


    Thanks Bryce and Colio but I can not take credit for initially creating it. I can not remember where I found it originally but made sure I kept a copy as I figured it would come in handy in the future. I think It was with a post on here a year or two back (someone trying to do a bank reconcilliation).

  • Vlookup Part Of String

    Re: Vlookup Part Of String



    Have you tried to use the mid and len functions? Also is the value only in C2 or are you actually wanting to combine tow values to look up?


    first, assume you only want to look up portion of just c2 then try:


  • If And Then Formula

    Re: If And Then Formula


    Quote from weeble5746

    The project has a contracted labor cost (i.e., $100,000);
    8% Retainage is held on first 50% of Labor Cost for the project;
    0% Retainage is held on 51-100% of Labor Cost for the project.


    Contractors will submit payment requests at various points in the project, and I need to accurately calculate the retainage withheld from each request.


    Simple Answer: If A2 = Contract amount (100,000) and B2 = the payment request amount (i.e. 50,000) in C2 put the following:


    Code
    =IF(B2<=(A2/2), (B2 - (B2*0.08)),B2)


    This will return 46,000


    Now, of course this does not take into effect cumulative payments so you will need to figure out how to hanlde that.

  • Clear Borders From All Cells

    Re: Border Class


    Quote from norie

    Since you aren't referencing any sheet for the range VBA will assume you mean the active sheet.


    Perhaps adding a worksheet reference might help?


    Well, it has something to do with borders on the cells. If I select the entire sheet and remove all borders (when I unprotect and unmerge), the it will work without adding the extra row.


    Is there a shorter way of doing it rather than all this:


    Code
    'remove all borders
    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone