Posts by her.rockstar

Thank you for your help anyway, this was solved for me at https://www.excelforum.com/exc…sed-on-date-selected.html

I've got some code started, but it's not working, and even still it would need to shift down one row.

Private Sub AddSchedule_Click()


Dim rowCell As Range
Dim colCell As Range
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim valChange As Double
Dim strCol As String
Dim strRow As String
Dim descrip As String


Set wsSource = Worksheets("Calendar")
Set wsDest = Worksheets("Records")


With wsSource
    shifts = .Range("E14:AI14").Value
    strCol = .Range("AR1").Value
    strRow = .Range("AQ1").Value
End With


With wsDest
    Set colCell = .Range("1:1").Find(strCol)
    Set rowCell = .Range("A:A").Find(strRow)
    
    With .Cells(rowCell.Row, colCell.Column)
        .Value = shifts
    End With
End With




End Sub

Hello
I am building a schedule record keeper.

I have two sections, one for viewing and one for adding. The View section works fine, but I'm looking to create a macro for my "Add Schedule" button that will transfer the month's-worth of data to the Records sheet, and then down one row, based on the month selected. The records to be transferred are highlighted in yellow.

[ATTACH]n1213196[/ATTACH]

Thank you for your help!

Never mind! Got it!

=VLOOKUP(B$1,Matrix!$B$1:$HQ$30,MATCH(B2,Matrix!$A$1:$HQ$1,0)-1,0)

You guys have both been great. This is the formula I ended up with, to match my records:

Quote

=INDEX(Matrix!$A$1:$HQ$30,1,MATCH(SMALL(OFFSET(Matrix!$E$3:$HQ$3,COLUMN(B$1)-2,0),ROW($A1)),OFFSET(Matrix!$E$3:$HQ$3,COLUMN(B$1)-2,0),0)+4)

What if I, now, would like to instead capture the distance number, and not the column header? So basically stop half way..? Any thoughts?

Thank you both so much! I found Jonathan's easier to work with, as I my actual column headers were named quite differently and the formula became confusing. I know the table reference way is probably more correct, but either way it's working. Thanks again!

I have a matrix with addresses on the left, and places across the top. Within the matrix are numbers (distances in km). On Sheet 2, I would like to compile lists for each address, showing the names of the 10 closest Places, sorted from shortest to furthest.

Re: Named Range based on corresponding columns in table

That's exactly what I was hoping for. Thanks man!

Re: Named Range based on corresponding columns in table

Sure thing, sorry about that.

In the example spreadsheet, there would be three named ranges: Boilermaker, Pipefitter, Labourer. The named ranges would grow or shrink, depending on what is selected from the drop downs in the table.

Elsewhere in the workbook I would like to list all the names from each Named Range.

Hi, so I have a table of workers. Their names are in column A. Their trade is in column C.

I would like to have a named range for each trade. The thing is, if they change trade via drop-down, their name should show up in the corresponding named range. Thanks for your help. Hope it's clear enough.

forum.ozgrid.com/index.php?attachment/74019/

Re: Exact same formula different results.

I figured it out!

I had to wrap the second COUNTIFS in a SUMPRODUCT. Bingo. Thanks again!

Re: Exact same formula different results.

So odd. The first picture is showing the formula, which is returning a result of 2 in O1.
[ATTACH=CONFIG]72738[/ATTACH]
The second picture is showing the results of the formula in the bar, which is 2,0,1 (3).
[ATTACH=CONFIG]72739[/ATTACH]

Re: Find the maximum date within date range

This works perfectly. Again, thanks.

Re: Exact same formula different results.

Yeah it's really strange. It appears fine when the company is set to Overall, but when I choose "b", for example, the math doesn't add up.

I think I must have my LTI, MA MD string in the wrong part of the formula.

Also, I've got the results of those two formulas in U1 and V1

U1 should return all injuries for the selected company/time frame

V1 should return all MA, LTI, MD injuries for the selected company/time frame

forum.ozgrid.com/index.php?attachment/72736/

Hello, I'm trying to use max to find the highest 'partial' date.

I have a range of:

42977.00
42976.00
42977.01
42976.01
42979.00
42976.02
42977.09

42977 represents 8/29/17
42977.07 is a little later in the day.

I need to find the highest instance of 42977, before it reaches 42978

Re: Exact same formula different results.

Would somebody perhaps be able to help me organize this formula properly?

This is the original:

=IF($J$3="Overall",SUMPRODUCT(COUNTIFS(Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>","<"&$G$42),Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>",">="&$N$3))),COUNTIFS(Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>","<"&$G$42),Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>",">="&$N$3),Injuries!A2:A1000,$J$3))

The new formula is trying to count Injuries!D2:D1000,{"LTI";"MA";"MD"} , but it's not returning the right number. For example,

Here's how I modified the formula:

=IF($J$3="Overall",SUMPRODUCT(COUNTIFS(Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>","<"&$G$42),Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>",">="&$N$3),Injuries!D2:D1000,{"LTI";"MA";"MD"})),COUNTIFS(Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>","<"&$G$42),Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>",">="&$N$3),Injuries!A2:A1000,$J$3,Injuries!D2:D1000,{"LTI";"MA";"MD"}))

The first formula is returning 2. The second formula should only return 1, but instead it's returning 0. Any thoughts if I'm phrasing that countif properly?

Re: Indexing a list in order, based on multiple criteria

I am just amazed at how you wrap your head around this stuff. So good.

Re: Indexing a list in order, based on multiple criteria

Yeah, basically. All injuries that occurred between 8/27 and ...9/2, then 9/3 to 9/9, then 9/10 to 9/16, etc.

Re: Indexing a list in order, based on multiple criteria

Ahh, questions!

So, because I'm not smart enough to know otherwise, I took the long way here: If I have two injuries on 8/27/17, I made it so the second one increases by .01, in order for them not to be the same. When I select the week of 8/27/17 from my time frame, I'm only seeing once instance.

I think in all my posting here I forgot to include the part where it's a week-long window.

Re: Indexing a list in order, based on multiple criteria

lol, I think I was too excited about the top part. I like your thinking
And thanks, once again.