Thank you for your help anyway, this was solved for me at https://www.excelforum.com/exc…sed-on-date-selected.html
Posts by her.rockstar
-
-
I've got some code started, but it's not working, and even still it would need to shift down one row.
Code
Display MorePrivate 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!
-
-
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.
-
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
-
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.0942977 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.