Bump - any ideas?
Bump - any ideas?
I have solved this with IF statements:
=IF(MATCH("customer contact details",Sheet3!A:A,0)-MATCH("address:",Sheet3!A:A,0)=3,OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0),IF(MATCH("customer contact details",Sheet3!A:A,0)-MATCH("address:",Sheet3!A:A,0)=4,OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),3,0),IF(MATCH("customer contact details",Sheet3!A:A,0)-MATCH("address:",Sheet3!A:A,0)=5,OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),3,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),4,0),IF(MATCH("customer contact details",Sheet3!A:A,0)-MATCH("address:",Sheet3!A:A,0)=6,OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),3,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),4,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),5,0),OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),1,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),2,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),3,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),4,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),5,0)&", "&OFFSET(INDIRECT("sheet3!"&"A"&MATCH("address:",Sheet3!A:A,0)),6,0)))))
This has the following limitations:
1. It only works in 2007 format (it exceeds reference limits on earlier versions).
2. It only works with 2 to 6 lines of address - if I were applying this to other lists with more lines, it would not work. Is there a way of making this work with x number of lines?
3. It is a mess!
Any help or suggestions to improve this would be much appreciated.
Thanks in advance.
Last edited by tone640; 08-08-2011 at 05:43 AM. Reason: Updated attachment
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks