Link to customer address in other worksheet - complicated

[Pixiepea]

How do I fill in my customers address automatically in my invoice
without any blank rows?

I have an "invoice" sheet setup where I choose the customer name from
dropdown, this then takes the customer contact details from th
"customer" sheet. The customer sheet is one customer per row, an
there are several address columns which might not be used for al
customers.

How do I get the address on my invoice to show without any blank rows?

For instance, now it might return:

Mr N Oone (Value from A1)
The company (Value from A2)
12 Street (Value from A3)
(blank) (Value from A4)
(blank) (Value from A5)
London (Value from A6)
SW10 0XX (Value from A7)

Is there anyway to avoid the blank lines?

The code used to find, say, the customer name is as follows (J2 is th
dropdown where I choose the company name):
=IF(ISNA(VLOOKUP($J$2,CompanyInfo,1,FALSE)),"",VLOOKUP($J$2,CompanyInfo,1,FALSE))

Please - if someone could help I would really appreciate it.

Or can I avoid printing the blank cells...? Like in Filemaker


Thank

 

[Debra Dalgleish]

As answered to your earlier question in excel.worksheet.functions:

'==========================
You could add a line to your macro that hides the rows with no contact
information.

If the cells on the Customers sheet are empty, the VLOOKUP should return
a zero. The following code selects cells in which a formula returns a
number, and hides those rows.

Worksheets("Invoice").Range("G5:G13") _
.SpecialCells(xlCellTypeFormulas, 1) _
.EntireRow.Hidden = True

To do this manually, you'd select cells G5:G13, and choose Edit>Go To.
Click the Special button, and select Formulas. Uncheck all except
Numbers, then click OK. Choose Format>Row>Hide.
'===========================

 

[Pixiepea]

Sorry - posted this before I saw you reply.

Have asked a few more things in reply, to your reply, on my othe
post... (this is getting confusing)

Copying them here to:

Q1 ____________
Ok, think I understand.

Do you mean that I hide the blank rows in the address part on th
"invoice" sheet - but that would then affect the look of each invoic
wouldn't it? Everything under the address would slide up and dow
depending on how long the address is, wouldn't it?

I know it is only 2-4 rows maybe, so it might not be the worst thing i
the word, but is there a way to avoid that?

Thanks for your reply!!!

Q2______________
Oh, sorry to keep adding - just an idea, is there any way of mergin
cell G5:G13, and write a code that returns all the customer addres
details separated by line breaks - and get that to skip the blan
cells