Pulling in information from other WorkSheets

[SlowlybutSurely]

I am having a hard time coming up with a formula that would pull information as long as a name and a date are the same. So, I need the employees name and the date referenced and then enter specific data that is in a different worksheet. I hope the way that I am explaining it makes sence. I am thinking some kind of IF formula but I cannot think of how to make it dependent on two different cells data.

[JLGWhiz]

Let's say that you want to return the value in sheet 2 cell D10 to cell A2 on sheet 1 if Sheet 1 cell B2 contains the text Yes and Sheet 1 Cell C2 contains the text No. In Cell A2 put

=IF(AND(B2="Yes",C2="No"),Sheet2!D10,"")

[SlowlybutSurely]

If I want to return the value in sheet 2 cell D10 to cell A2 on sheet 1 if Sheet 1 cell A2 contains the text yes and Sheet 2 cell D2 contains the text yes and Sheet 1 Cell A3 contains the text no and Sheet 2 cell D3 contains the cell the text no how would I reference that?

[dominicb]

Good afternoon SlowlybutSurely

As this might be a difficult concept to explain, can you upload an example sheet showing precisely what your issue is and what your expected outcomes are?

DominicB



Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitise the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[SlowlybutSurely]

So what I am trying to do is pull the percentage from Sheet 2 Column A into Sheet 1 Column K into Sheet 1 as long as the name "T2" and the Date are the same.

[dominicb]

Hi SlowlybutSurely

OK, that shouldn't be too bad.

One question - the results sheet (After1) shows only results that match, there are no blank rows where matches don't occur. Is this correct - no blank rows allowed?
If so, this will require a VBA solution - are you OK with that?

One more thing - I might not get to this today, but I will come back to it, so if no-one else jumps in, keep checking back...

HTH

DominicB

[SlowlybutSurely]

There could be a blank row if there was an error, with this section there just weren't any. Blank rows are allowed

Thank you so much!

[JLGWhiz]

This would require an array formula, which I have very little expertise in, if you have to use a formula. But I can offer a solution using VBA. This code would be copied to the standard code module1. The workbook would need to be saved as a macro enabled workbook.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, fAdr
Set sh1 = Sheets(1) 'Substitute sheet name in quotes for index #
Set sh2 = Sheets(2) 'Substitute sheet name in quotes for index #
    With sh1
        For Each c In .Range("B2", .Cells(Rows.Count, 2).End(xlUp)) 'Assumes header row 1
            Set fn = sh2.Range("C:C").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fAdr = fn.Address
                    Do
                        If Trim(fn.Offset(, 2).Value) = Trim(c.Offset(, -1).Value) Then
                            c.Offset(, 9) = fn.Offset(, -2).Value
                            Exit Do
                        End If
                        fn = sh2.Range("C:C").FindNext(fn)
                    Loop While fn.Address <> fAdr
                End If
        Next
    End With
End Sub

[SlowlybutSurely]

Ok forgive me, I've never used VBA before. the Sub t() should I be entering information there to make it work for me?

[JLGWhiz]

To use the macro in post #8
1. Make sure your workbook is saved as a macro enabled workbook with file extension .xlsm
2. Press Alt + F11 to open the code editor
3. If the large pane is dark, then click 'Insert' on the editor tool bar, then click 'Module'
4. When the large pane of the editor is bright, copy the code above and paste it into the large pane and save the file.
5. Close the editor window.

To run the procedure from the Excel window
1. Press Alt + F8
2. When the dialog box appears, left click the macro name.
3. Click 'Run'

If the macro does what you want it to do, you can assign it to a button and run the macro by clicking the button.
1. Cick 'Developer' on the ribbon.
2. Find and click 'Insert' on the bottom line of the tool bar.
3. You should see two sets of controls.
4. Click the button in the Form Controls tool box.
5. Using the mouse pointer, put the cross hairs (+) symbol where you want your button to reside and Left click.
6. The button should appear along with a dialog box to assign the macro.
7. Double click the macro name.

You should now be able to run the macro by simply clicking the button.