Posts by richadj4

    Re: Search table return multiple value's

    Okay, Yes this is possible with formulas, quick and dirty, this ARRAY formula in B3:
    (Note ARRAY formulas MUST be enter with Ctrl Shift Enter, so paste the above text into that cell, and instead of pressing enter, press ctrl + shift + enter)

    Then copy this formula across 1 and down "several" (If you are think 2 - 3 answers, copy it down 6 lines to be safe)

    Things to be aware of:
    This will handle up to 5000 lines of data in the F-H table. you SHOULD be using dynamic named ranges instead of absolute addresses (which I can explain if needed), but this will get the job done.
    Happy to describe a detailed explanation of the formula IF YOU WANT IT
    It MAY look like it's not working as you get a value like 0.354 in the time column, this is just formatting, it's displaying 8:30 as a decimal number, just change the format in this column (or copy paste format only from the time column)

    Re: Choose text from drop down list will populate lists of texts automaticaly in next

    Delete the "*" you currently have in cells A7 - A15, Then it should add things from A7 downwards. Currently it stops at 23 (cause that the size of the table), If you want it to go further then change

    [COLOR=blue]If[/COLOR][COLOR=#333333] Target.Parent.Cells(65000, 1).End(xlUp).Row < 23 [/COLOR][COLOR=blue]Then[/COLOR]

    this to a number higher than 23, or delete it (and the endif) entirely

    Re: copy cells from one sheet to another when criteria is met.

    Would be easier with a sample sheet, and I'm ASSUMING you meant "copy the whole ROW".

    Further assuming % is in column 8, main sheet is Sheet1, other sheet is Sheet2 and there may already be data on the second sheet:

    Sub sample2()
    Dim rdata As Range
    Dim c As Range
    Set rdata = Range(Sheet1.Cells(2, 8), Sheet1.Cells(65000, 8).End(xlUp))
    For Each c In rdata.Cells
        If c.Value > 1 Then
            Sheet2.Cells(65000, 8).End(xlUp).Offset(1, 0).EntireRow.Value = c.EntireRow.Value
        End If
    End Sub

    Re: Choose text from drop down list will populate lists of texts automaticaly in next

    I love the cellformat trick, I haven't seen that before.

    Unfortunately for the rest of that, my understanding of the requirements was that changing the drop down would ADD names to the bottom of the table, not replace ones that are there, making a non-vba solution impossible (without multiple drop down boxes)

    Re: Vlookups, Vba, Multiple results

    Variety of possibilities. If you are looking for speed I'd suggest either a find loop, or testing every single line (faster than you'd think). The REALLY important part is not to write each individual response back, write to a variant array, and write that back in 1 sweep.

    something like this:

    Re: Choose text from drop down list will populate lists of texts automaticaly in next

    This code should be on sheet1. Additionally you will need to set B3 to the named ranged "meeting", and the names on sheet2 to the named ranges "Enviroment", "Audit", and "Planning" (Ideally these should be dynamic named ranges, but if you're unsure normal ones will work.

    It SHOULD be fairly obvious how to extend this for more categories etc.

    Re: Power Forecast Formula Errors Out on Legitimate Value

    Found it. "part" of your formula is EXPing a value, with 131,000 the value you are EXPing is 709.93 which = 2.8E+307 with 130,000 the value you are (attempting) to EXP is 713.82. this is more than 9.9E+307 (which is the biggest number excel can handle). Therefore it dies.

    Don't know enough about the maths to suggest how you can FIX it, but that's where your issue is and why

    Re: Tracking hours and pay outstanding in spreadsheet

    Okay, this is physically painful to look at, but I'll take a stab.

    I'm HOPING that your main issue is in column H, which is SUPPOSED to be a sum of the non snow hours done that week. I'll try to stay close to what you are doing formula wise, but some things just HAVE to be changed.

    Long (mostly English) answer, column H should be the cell above, + plus the cell to the left UNLESS, it's a weekend, day after weekend or snow day. If it's a weekend, it should just be cell above. If it's day after weekend, it should just be cell to the left. That much is working. I THINK you've done snow incorrectly. If it's a snow day you've told it to be 0, but you should really have told it to be day above (don't want to ADD snow hours, but want to carry forward work hours from above). Finally you then have issues with BOTH a snow day AND day after weekend, which would get you pretty screwed up nested ifs.
    There is a better approach. In english, H should be the cell above (unless yesterday was a week end) + cell to the left (unless today is a weekend, or a snow day), so........

    Short (mostly formula) answer 1: H271: =IF(OR(A270="week end",A270="sunday"),0,H270)+IF(OR(A271="week end",J271="snow"),0,G271)

    This APPEARS to work correctly in column H.

    Re: Count number of zeros that are within a range of consecutive zeros

    If you NEED an answer that does not use VBA at all, and have spare columns, this will do it:
    Cell AY2 = "=IF(SUM(A2:R2)=0,IF(AX2>0,1,18),0)" fill across to CC2 and then down to bottom of table.
    Then a simple "=SUM(AY2:CC2)" in AW2 (and filled down) will provide accurate counts.

    Re: Help with Reset Counter when condition Met

    Okay, there are a variety of ways to do this, this is a "pretty" one, which doesn't require VBA at all.

    I'm using the range B1:B1000 for this example. This WON'T work with B:B, so you DO need to specifcy less than the entire column. The "best practice" way to do this is with dynamic named ranges (which I'm not doing here, to make it easier to read)

    Put this formula "somewhere" (I've chosen A1) :"=IFERROR(COUNTIF(OFFSET(B1:B1000,LARGE(IF(B1:B1000="E",ROW(B1:B1000)-ROW(INDEX(B1:B1000,1,1))+1),1),0),"X"),COUNTIF(B:B,"X"))". This MUST be entered as an array formula (paste it, and then instead of enter, hit ctrl+shift+enter)

    I can give you a detailed explanation if you need it, but this basically finds the part of the range "B1:B1000" that is below the last "E" and counts the X's in it. it will automatically update as you enter new information.

    Re: Copy occurrences from one cell in a column to another

    ug. For some reason in your example, the text in column A has SSS-DEWBU but the "result" in columns B-D has SSS-T5WBU. I'm not sure if this is a typo or has some significance I'm not understanding.

    The way I have read your question, the following formula in B1 can be copied across and down to generate the results I THINK you are looking for:

    Some caveats:
    This assumes that the start of the text will always be SSS-DEWBU
    This assumes the desired result will always be 14 characters
    This assumes the character "!" will never legitimately be in the source text anywhere ("!" can be replaced by any character, or indeed string of characters that you can guarantee will never be present)
    If the formula is copied right into a fourth column it will generate an error (as there are only 3 instances), this can easily be solved by wrapping the formula in an iferror formula if you have an unknown number of instances.

    Re: Copy Certain Columns to Multiple Sheets

    uhhhhhhhh, questions:

    1) You are using the word "copied", does it need to be copied? or do you just want sheet4 to contain the appropriate info from the master sheet?
    2) It would be...... relatively simple to set formulae on sheets 2-5 that would autofill the appropriate values from the master as they were entered.
    3) It would be relatively easy to write vba to fill values on sheets 2-5 from the master sheet when run

    Re: Choose text from drop down list will populate lists of texts automaticaly in next

    Theoretically possible, but very messy.

    Using VBA it is POSSIBLE, you need to include a "please select" or Please choose" or similar in the list, and implement code on the worksheetchange event to populate cells when something is added (more information can be provided if this is the way you want to go)

    Without VBA, you COULD have several dropdown menus across row 3 (B3, D3, F3 for example) and formulae to fill the table based on the values in those boxes (more information can be provided if this is the way you want to go).

    Re: Search table return multiple value's

    1) are you searching multiple columns for the text in A3? (as written this would only return a result if the A3 text is in column AI)

    2) Do you have an upper bound on the number of results?

    3) Is column AI sorted, or can it be?

    4) Are you looking for a formulae solution (possible but difficult-very difficult depending on above answers) or is VBA acceptable?

    5) sample data?