Excel spreadsheet formula question

Here's what I want to do:

Sheet1 has a quick form to fill out that includes name, address1, address2, and city/state/zip fields.

Sheet2 grabs this information for uniform printing. (yeah, yeah, this should all be done in Access, at least, but it's not going to happen that way!)

When there isn't a need for the Address2 line, I want Sheet2 to be able to come in and remove the null value and then bring up the city/state/zip value.

Example:
Name: Bob Test
Add1: 123 main street
Add2:
Add3: NY, NY 11111

On sheet 2, I want it to say:
Bob Test
123 Main Street
NY, NY 11111

It currently says:
Bob Test
123 Main Street
0
NY,NY 11111

How can I get it to copy the 3rd field into the 2nd field if it has a null value?

Confused yet?

 

Assuming the source sheet data fields are A1...A4, then:

Try this for add2: formula on the target sheet:

=IF(A3>" ",A3,A4)

and for add3: formula on the target sheet:

=IF(A3>" ",A4," ")

You'll have to add the page being sourced to the formula of course. (A3:sheet1)

That will give you a three line address when occurs, without a space.

 

Okay, I tried it, and madea modification to the formula, and now I'm REALLY stumped.

Here's what I'm doing:
On sheet 1:
B1: Bob
B2: street
B3: null or apt.
B4: city

On sheet 2, the formulas are:
C1: =sheet1!b1
C2: =sheet1!b2
c3: =IF(sheet1!b3=" ",b4,b3)
C4: =IF(sheet1!b3=" "," ",b4)

However - this doesn't work, but it should work. Here's the part that stumps me. I started off by doing these formulas all on one page:

A7: Bob
A8: street
A9: null or apt.
A10: city

D7: =A7
D8: =A8
D9: =IF(A9=" ",A10,A9)
D10: =IF(A9=" "," ",A10)

Can anyone tell me how a formula can work on one sheet, but can't work between sheets???

I'm stumped!!!!

 

Try these formulas in your C3 and C4 cells respectively:

=IF(Sheet1!B3>" ",Sheet1!B3,Sheet1!B4

=IF(Sheet1!B3>" ",Sheet1!B4," ")

Should work. You just forgot to use the Sheet id for each source cell reference. The > symbol is also necessary. You can use the < symbol also, it makes no diff.

 

thank you! It works now!