Good morning,
Workbook A Sheet 1 contains four columns
A = concatenate B & C
B = first name
C = cast name
D = phone number
The CONCATENATE formula in column A replicates down to line 50, as i can't be sure how many contacts there will be, but i do know that there won't be more than 50.
Workbook B Sheet 'Contacts' contains a query to fetch the data from Workbook A Sheet 1 on startup and displays the data in the same format as the source worksheet.
Workbook B Sheet 'Main' then contains a ComboBox that fetches data from column A from Workbook B 'Contacts' sheet.
However, the ComboBox has lots of blank rows.
How do i get rid of these?
The ComboBox is populating from a data validation drop-down list in A1 - courtesy of Contextures code that enables a ComboBox to appear when you double-click on a cell that contains a data validation drop-down list.
The data validation list source is '=CONTACTS'
Within Name Manger, CONTACTS is defined as a dynamic range =OFFSET(CONTACTS!$A$1,0,0,COUNTA(CONTACTS!$A:$A),1)
(However, when you select the range in name manager, the range is showing down to row 50 - should it not highlight the whole column? I can't see where i am referencing it only down to row 50??)
I've tried deleting the CONCATENATE formula in original source workbook from the rows that do not contain any data, just to see, but it made no difference.
Any ideas much appreciated. Whilst not the end of the world and is workable, it is just one of these frustrating issues that i am sure is easy to sort if you know how.
Best regards
manc
Workbook A Sheet 1 contains four columns
A = concatenate B & C
B = first name
C = cast name
D = phone number
The CONCATENATE formula in column A replicates down to line 50, as i can't be sure how many contacts there will be, but i do know that there won't be more than 50.
Workbook B Sheet 'Contacts' contains a query to fetch the data from Workbook A Sheet 1 on startup and displays the data in the same format as the source worksheet.
Workbook B Sheet 'Main' then contains a ComboBox that fetches data from column A from Workbook B 'Contacts' sheet.
However, the ComboBox has lots of blank rows.
How do i get rid of these?
The ComboBox is populating from a data validation drop-down list in A1 - courtesy of Contextures code that enables a ComboBox to appear when you double-click on a cell that contains a data validation drop-down list.
The data validation list source is '=CONTACTS'
Within Name Manger, CONTACTS is defined as a dynamic range =OFFSET(CONTACTS!$A$1,0,0,COUNTA(CONTACTS!$A:$A),1)
(However, when you select the range in name manager, the range is showing down to row 50 - should it not highlight the whole column? I can't see where i am referencing it only down to row 50??)
I've tried deleting the CONCATENATE formula in original source workbook from the rows that do not contain any data, just to see, but it made no difference.
Any ideas much appreciated. Whilst not the end of the world and is workable, it is just one of these frustrating issues that i am sure is easy to sort if you know how.
Best regards
manc
