fitzchivalry
New Member
- Joined
- Mar 23, 2015
- Messages
- 33
Hi
I have a sheet where sales are recorded.
When I start a new row off in column C, the Date is inserted into A and Time into B. The code that does that I'll stick at the bottom of this post.
In column F an Identifier is entered, and once this happens I have a formula in G and H, they look up a Product Full name (based on the identifier) and in H the Product Type.
Defined Ranges used there are these by the way, if this matters:
TL;DR: I'd like when a cell in F is edited for the formulas given above to be inserted into columns G and H on the same row.
One way I could do this would be to insert those formula's into the next x rows, and keep copying down as necessary.
Perhaps once the row above is populated with data, the next row could automatically populate with the formulas.
---
For completeness/readability I'll give an example of what I want, before and after:
<tbody>
</tbody>
What I'm trying to illustrate in this next chart is how I want the formula's to be added to the row; with the row number changing to the corresponding row.
<tbody>
</tbody>
---
I mentioned this early in the post; code that inserts Date in A and Time in B when C is edited.
I have a sheet where sales are recorded.
When I start a new row off in column C, the Date is inserted into A and Time into B. The code that does that I'll stick at the bottom of this post.
In column F an Identifier is entered, and once this happens I have a formula in G and H, they look up a Product Full name (based on the identifier) and in H the Product Type.
Code:
=INDEX(ProductFullName,MATCH($F2,ProductID,0))
Code:
=INDEX(ProductType,MATCH($F2,ProductID,0))
Defined Ranges used there are these by the way, if this matters:
Code:
ProductType = 'ProductList'!$C:$C
ProductID ='ProductList'!$A:$A
TL;DR: I'd like when a cell in F is edited for the formulas given above to be inserted into columns G and H on the same row.
One way I could do this would be to insert those formula's into the next x rows, and keep copying down as necessary.
Perhaps once the row above is populated with data, the next row could automatically populate with the formulas.
---
For completeness/readability I'll give an example of what I want, before and after:
Date (A) | Time (B) | Order (C) | Product ID (F) | Product Full Name (G) | Product Type (H) |
01/03/2015 | 12:00 | YY970 | GOS.RQQ | =INDEX(ProductFullName,MATCH($F2,ProductID,0)) | =INDEX(ProductType,MATCH($F2,ProductID,0)) |
08/04/2015 | 13:15 | GK420 | KGP.RQQ | =INDEX(ProductFullName,MATCH($F3,ProductID,0)) | =INDEX(ProductType,MATCH($F3,ProductID,0)) |
15/05/2015 | 14:30 | HQ550 | SFF.DEP | =INDEX(ProductFullName,MATCH($F4,ProductID,0)) | =INDEX(ProductType,MATCH($F4,ProductID,0)) |
29/06/2015 | 16:19 | JV280 |
<tbody>
</tbody>
What I'm trying to illustrate in this next chart is how I want the formula's to be added to the row; with the row number changing to the corresponding row.
Date (A) | Time (B) | Order (C) | Product ID (F) | Product Full Name (G) | Product Type (H) |
01/03/2015 | 12:00 | YY970 | GOS.RQQ | =INDEX(ProductFullName,MATCH($F2,ProductID,0)) | =INDEX(ProductType,MATCH($F2,ProductID,0)) |
08/04/2015 | 13:15 | GK420 | KGP.RQQ | =INDEX(ProductFullName,MATCH($F3,ProductID,0)) | =INDEX(ProductType,MATCH($F3,ProductID,0)) |
15/05/2015 | 14:30 | HQ550 | SFF.DEP | =INDEX(ProductFullName,MATCH($F4,ProductID,0)) | =INDEX(ProductType,MATCH($F4,ProductID,0)) |
29/06/2015 | 16:19 | JV280 | KJV.LKK | =INDEX(ProductFullName,MATCH($F5,ProductID,0)) | =INDEX(ProductType,MATCH($F5,ProductID,0)) |
<tbody>
</tbody>
---
I mentioned this early in the post; code that inserts Date in A and Time in B when C is edited.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.EnableEvents = False
For Each Cell In Target
If Cell.Column = 3 And Cell <> "" Then
If Range("A" & Cell.Row) <> "" Then
If MsgBox("Date/Time stamps exists in row " & Cell.Row & _
", do you wish to change them?", vbYesNo, _
"Change time stamps?") = vbYes Then
Range("A" & Cell.Row) = Date
Range("B" & Cell.Row) = Time
End If
Else
Range("A" & Cell.Row) = Date
Range("B" & Cell.Row) = Time
End If
End If
Next Cell
Application.EnableEvents = True
End Sub
Last edited: