Data validation, IF, OFFSET & COUNTA

OK i have a table on a sheet that i enter Jobs to, address, job number and quoted costs.

I aslo have a sheet that i enter my invoice details into (note this is not an actual invoice its just the table where i keep a record of all invoices).

On my invoice sheet i have a drop down list to select Job number, now its easy enough using OFFSET and COUNTA to do data validation for any and only job numbers entered in the job sheet.

Here's where i wanna get tricky - A) Is there a way that when i click the cell with drop down it shows either the Job number AND job address (both of which is entered in the job sheet) but only populates the cell with the job no when i make a selection? the reason being its easier to select the right job number when there is the address next to it or else your clicking back and forth between sheets to check you picked the right number?

B)To somehow incorporate COUNTA so that the job numbers plus addresses displayed in drop down list are only jobs that are labelled "Open" this information is also in the job sheet. the reason being i don't want to scroll through hundreds of jobs each time i use the drop down as 95% of those jobs are closed and unnecessary to be seen in the drop down.

Hope I've made sense any help greatly received.

Hi,

Can you please upload your workbook to make it easier for me to help you?

Thanks