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.
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.
