Lookup specified cell contents (lookup the contents of E3) from all columns in table across multiple sheets and return corresponding value from one co

[Steve1208]

I have been stuck for weeks trying to solve the problem mentioned in the title.

Let me try and make it as clear to under stand as possible.

I have a workbook that I want to populate with an address vertically.
Each line of the address will fill one cell:
C5 - Line 1
C6 - Line 2
C7 - Line 3
C8 - Line 4
C9 - Line 5

The addresses are located on another document and have been populated on 5 separate sheets. Each sheet is populated with an identical layout.

These are written horizontally across the table:
C4 - Line 1 (House number)
D4 - Line 1 (Road name)
E4 - Line 2
E4 - Line 3
F4 - Line 4
G4 - Line 5


I am looking for a formula that will search a cell contents (say C3) and return the relevant part of the address to the cells I want to populate.
The cell contents in C3 can contain any three key words that will return the result, so you can search for the address using either the post code, job number or tenants name.

I have tried a formula, which works to populate the cells, but it will only work for one sheet, if I want it to recognise all sheets I have to add the whole formula again referring to the next sheet and so on. This makes the formula very long to type and difficult to replicate when more addresses are added on another document.

This is the formula I am currently using to achieve the function I want, but as I mentioned this only refers to one sheet:

=C3&" "&IFERROR(IFERROR(IFERROR(INDEX('[Surveys - CTC - 2017-2018 copy.xlsx]Phase 1'!$D$4:$D$1048576,MATCH("*"&E3&"*",'[Surveys - CTC - 2017-2018 copy.xlsx]Phase 1'!$F$4:$F$1048576,0)),INDEX('[Surveys - CTC - 2017-2018 copy.xlsx]Phase 1'!$D$4:$D$1048576,MATCH("*"&E3&"*",'[Surveys - CTC - 2017-2018 copy.xlsx]Phase 1'!$I$4:$I$1048576,0))),INDEX('[Surveys - CTC - 2017-2018 copy.xlsx]Phase 1'!$D$4:$D$1048576,MATCH("*"&E3&"*",'[Surveys - CTC - 2017-2018 copy.xlsx]Phase 1'!$G$4:$G$1048576,0))),"")

I hope this makes sense.

I would be really grateful for some advice on how to condense this formula down and maybe simplify it.
Also if there is a way to future proof the formula. By this I mean, if another sheet was to be added to the document, the formula would extend its search to the new sheet.

 

[mumps]

You may need to clarify that in your post, you have two E4's, one for Line 2 and one for line 3. Have you considered using a macro instead of a formula?

 

[Steve1208]

That is a typo on ny original post.

If anyone could advise a VBA code to solve that problem that would be brilliant. In fact VBA could be better.

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your 2 files to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link for each file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Steve1208]

I have up loaded the following documents:
"Primary working sheet" https://www.dropbox.com/s/79ffwfltvw98wvs/Primary working sheet.xlsx?dl=0
"Address Sheet" https://www.dropbox.com/s/j2aohhde6se026x/Address Sheet.xlsx?dl=0


The primary working sheet is where the formulas are located and this is the sheet I want to populate the information on.
The address sheet is where all the date I want to use is located.

Because of sensitive information I have filled the cells with generic information.

Please let me know if the formula does not populate on the excel page and I will send it on the forum.


I really appreciate any help with this.
Thanks in advance.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your CHECKLIST 2 sheet in the "primary working sheet" workbook and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Save the "primary working sheet" workbook as a macro-enabled file. Make sure that the "Address Sheet" workbook is open. Enter a value in E3 of your CHECKLIST 2 sheet and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E3")) Is Nothing Then Exit Sub
    Dim LastRow As Long
    Dim foundVal As Range
    Dim srcWB As Workbook
    Set srcWB = Workbooks("Address Sheet.xlsx")
    Dim ws As Worksheet
    For Each ws In srcWB.Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set foundVal = ws.Range("A5:I" & LastRow).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundVal Is Nothing Then
            Range("C4") = ws.Range("G" & foundVal.Row)
            Range("C5") = ws.Range("C" & foundVal.Row) & " " & ws.Range("D" & foundVal.Row)
            Range("C6") = ws.Range("E" & foundVal.Row)
            Range("C9") = ws.Range("F" & foundVal.Row)
            Range("H4") = ws.Range("I" & foundVal.Row)
            Range("H5") = ws.Range("A" & foundVal.Row)
            Range("H6") = ws.Range("B" & foundVal.Row)
            Range("H7") = ws.Range("H" & foundVal.Row)
        End If
    Next ws
End Sub

Keep in mind that if you enter a postal code in E3 and there are more than one tenant with the same postal code, only the first occurrence of that postal code will be found.

 

[Steve1208]

HI Mumps

Thanks for this.
It works perfectly, however it does come up with one Error.
" LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row"

Not sure what is causing this.

Can I just ask one more thing. How easy would this be to put into a short formula?
Or do you think it is best sticking with the VBA code?

 

[mumps]

Regarding the error, make sure that you don't have any sheets that are empty in the "Address Sheet" workbook . Given the length of your formulas, I think that you are better off sticking with the macro. Also, with the macro, you don't have to worry about future proofing the formula as the macro will automatically account for any new sheets that are added.

 

[Steve1208]

Hi Mumps

I understand. I will have a good read up on the VBA codes and the one you have kindly provided me with.
I just have one more question with this, which I should have indicated on the document I provided.

The address number needs to be populated from the "Primary working sheet" cell "C3", as when you are looking up a post code for an address this could populate the cells with the incorrect address number. Therefore to avoid mistake the house number needs to be keyed on the primary working sheet.

sorry to keep coming back at you with more questions.

Thanks again.

 

[mumps]

Change this line of code:

Range("C5") = ws.Range("C" & foundVal.Row) & " " & ws.Range("D" & foundVal.Row)

to

Range("C5") = Range("C3") & " " & ws.Range("D" & foundVal.Row)