(VLOOKUP) If cell is blank, use value from next cell?

[Pixiepea]

Help... I would really really appreciate if you could help.

I'm setting up an invoicing system in Excel, based on a template I hac
received which is using macros. I'm not sure if they are using th
simplest way to do this to be honest, and I am a real beginner wit
macros so not sure how to check how these affect anything (if they do)


I have one worksheet called "Invoice" and another called "Customers".
On the invoice sheet I use a dropdown (J2) to choose which customer th
invoice should go to, I then get the contact details filled i
automatically in, say, cells G5 to G13.

These are filled in as follows:
Company name
=IF(ISNA(VLOOKUP($J$2,CompanyInfo,1,FALSE)),"",VLOOKUP($J$2,CompanyInfo,1,FALSE))
Address 1
=IF(ISNA(VLOOKUP($J$2,CompanyInfo,4,FALSE)),"",VLOOKUP($J$2,CompanyInfo,4,FALSE))

..and so on...

However, I have 6 columns for address on the customer sheet - all o
these are not always used. How do I write the formula so that if th
lookup index (cell) is blank, go to the next cell and use that value
if that one is also blank go to the next cell and use that value...?

AND - if the cell DOES have a value in it, how do I move on to the nex
line on my invoice, to fill in the next value gathered from th
customer sheet?

Sorry, don't think I have explained this very well - I hope yo
understand me and can help - otherwise let me know and I''ll try t
explain.

Thanks
Pixiepea
:confused

 

[Debra Dalgleish]

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]

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

 

[Pixiepea]

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

 

[Debra Dalgleish]

If there's nothing else in rows 5:16, delete them, and create a formula
in G5 that concatenates the contact information. For example:


=IF(ISNA(VLOOKUP($J$2,CompanyInfo,1,FALSE)),"",VLOOKUP($J$2,CompanyInfo,1,FALSE))&IF(VLOOKUP($J$2,CompanyInfo,4,FALSE)="","",CHAR(10)
&VLOOKUP($J$2,CompanyInfo,4,FALSE))&IF(VLOOKUP($J$2,CompanyInfo,5,FALSE)="","",CHAR(10)
&VLOOKUP($J$2,CompanyInfo,5,FALSE))

Set the cell to Wrap Text, or the Char(10) will appear as a small
square, instead of a line break.

 

[Pixiepea]

That is absolutely perfect - thanks a lot!! Have struggled with this fo
such a long time before I found this forum - thank you, thank you.

Sorry about the multiple posting...

:

 

[Debra Dalgleish]

You're welcome, and thanks for letting me know that it worked.