Posts by richadj4

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

MUCH prefer Smallman's solution, but should really turn the filter off again when done

Range("H1:H" & Cells(Rows.Count, 8).End(xlUp).Row).AutoFilter

and be aware this won't work unless sheet1 is the active sheet

Re: Search table return multiple value's

Okay, Yes this is possible with formulas, quick and dirty, this ARRAY formula in B3:
=IFERROR(OFFSET($F$1,SMALL(IF($F$2:$F$5000=$A$3,ROW($F$2:$F$5000)-ROW(INDEX($F$2:$F$5000,1,1))+1),ROW()-2),COLUMN()-1),"")
(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
Next
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:

Sub wut()
Dim rData As Range
Dim vData As Variant
Dim i As Long
Dim rfound As Range
Dim rfirst As Range
If Application.WorksheetFunction.CountIf(Sheet2.Columns(1), Sheet1.Cells(1, 1).Value) = 0 Then Exit Sub
Set rData = Sheet1.Cells(2, 1).Resize(Application.WorksheetFunction.CountIf(Sheet2.Columns(1), Sheet1.Cells(1, 1).Value), 2)
vData = rData.Value
Set rfirst = Sheet2.Cells(1, 1)
Set rfound = Sheet2.Columns(1).Find(Sheet1.Cells(1, 1).Value)
vData(1, 1) = rfound.Value
vData(1, 2) = rfound.Offset(0, 1).Value
i = 2
Set rfound = Sheet2.Columns(1).FindNext(rfound)
While Not rfound.Address = rfirst.Address
    vData(i, 1) = rfound.Value
    vData(i, 2) = rfound.Offset(0, 1).Value
    i = i + 1
Wend
rData.Value = vData
End Sub

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.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rdata As Range
Dim c As Range
    If Target.Address = ThisWorkbook.Names("meeting").RefersToRange.Address Then
    'If the dropdown was changed
        Select Case Target.Value
         Case "Environment and Community"
            Set rdata = ThisWorkbook.Names("Environment").RefersToRange
         Case "Audit and Risk"
            Set rdata = ThisWorkbook.Names("Audit").RefersToRange
         Case "Planning"
            Set rdata = ThisWorkbook.Names("Planning").RefersToRange
        End Select
        'Set rdata to the range of names to add
        For Each c In rdata.Cells
            If Target.Parent.Cells(65000, 1).End(xlUp).Row < 23 Then 'If there is still space in the table
                'If the name is not ALREADY present, add it to the last line
                If Target.Parent.Columns(1).Find(what:=c.Value) Is Nothing Then Target.Parent.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = c.Value
            End If
        Next
    End If
End Sub

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: CONCAT formula to use commas

Bit of a wild guess without seeing data/expected results, but generally (where do I place it) you'd replace F9 in the top formula with TEXT(F9,"#,##0")

Re: SUMIF using Variable Columns

Off by 1 error

Just add -1 after the $O$12 reference:
=SUM($O20:INDEX($O20:$S20,MATCH($O$12-1,$O$19:$S$19)))

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: Beforesave - re- enable events after error

Seems to be working for me. Whats the error, where's the error, what version of excel are you using and what's the value in A1 when you get an error?

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:
=MID($A1,SEARCH("!",SUBSTITUTE($A1,"SSS-DEWBU","!",COLUMN()-1)),14)

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: Sum alternating columns in a single row using if equation?

This formula in cell U2 can be copied across and down to give the correct results:
=SUMIF($B2:$T2,U$1,$A2:$S2)

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: Change value of two cells based on one cell VBA

"And" is incorrect.
Delete this word and place the code following it on the next line

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?