Add content (formulas) to two cells on same row, when data is entered in a cell on that row

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.

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/201512:00YY970GOS.RQQ=INDEX(ProductFullName,MATCH($F2,ProductID,0))=INDEX(ProductType,MATCH($F2,ProductID,0))
08/04/201513:15GK420KGP.RQQ=INDEX(ProductFullName,MATCH($F3,ProductID,0))=INDEX(ProductType,MATCH($F3,ProductID,0))
15/05/201514:30HQ550SFF.DEP=INDEX(ProductFullName,MATCH($F4,ProductID,0))=INDEX(ProductType,MATCH($F4,ProductID,0))
29/06/201516:19JV280

<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/201512:00YY970GOS.RQQ=INDEX(ProductFullName,MATCH($F2,ProductID,0))=INDEX(ProductType,MATCH($F2,ProductID,0))
08/04/201513:15GK420KGP.RQQ=INDEX(ProductFullName,MATCH($F3,ProductID,0))=INDEX(ProductType,MATCH($F3,ProductID,0))
15/05/201514:30HQ550SFF.DEP=INDEX(ProductFullName,MATCH($F4,ProductID,0))=INDEX(ProductType,MATCH($F4,ProductID,0))
29/06/201516:19JV280KJV.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:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi Fitzchivalry,

did you try recording a macro to catch those formulas? Macro recording is one of the easiest ways to get building blocks for your own macros. You'll need a small change of your current code. The recording should give something like:

ActiveCell.FormulaR1C1 = "=INDEX(ProductFullName,MATCH(RC6,ProductID,0))"
Then you can put that in your current block of code, like so:

Code:
    If Cell <> "" Then
        If Cell.Column = 3 Then
            'Column C filled by the user
            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
        ElseIf Cell.Column = 6 Then
            'Column F filled by the user
            Range("G" & Cell.Row).FormulaR1C1 = "=INDEX(ProductFullName,MATCH(RC6,ProductID,0))"
        End If
    End If

Cheers,

Koen
 
Upvote 0
That worked, thank you very much Rijnsent :), and because you didn't spoon feed me every last detail, I now understand how it works too :) (so I'll try and explain for anyone reading in future. Not sure if I'm really helping them actually!) - I replaced my code with your code block (leaving some of it, which I don't understand but presume does something - before and after it, and then added a couple of lines similiar to

Code:
Range("G" & Cell.Row).FormulaR1C1 = "=INDEX(ProductFullName,MATCH(RC6,ProductID,0))"

inserting the appropriate column where it says G and the appropriate formulas after the = between " ", adjusting RC6 to match the column number (in my case 6; column F) of the Product Identifier the match function requires.

So now when I type in C I get time and date stamp and then when I type in F I get INDEX MATCH formula's in G H I :D.

I haven't recorded a Macro in over 10 years (and didn't have a good idea of what I was doing back then)... to be honest it didn't even occur to me to attempt to use that functionality, I'll try that next time.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top