EXCEL Formula Simple Problem

[zteam777]

This one is simple, just need help with it.

I have 2 worksheets created in the same file. One labeled Work Order and one labeled Invoice.

They are exactly alike accept for the headings on each worksheet and a few slight differences.

I simply want whats typed on the some of the fields on the work order to be copied to the invoice to avoid typing the info twice.

Yes, I did go to the invoice field and type = then to the work order same field and press enter, that works fine, however on the invoice it leaves a 0 until something is entered on the work order.

The problem with that is some of the fields may not need info typed in on the work order thus leaving all of these 0's on the invoice.

I need the formula without it showing the 0's.

Please help.

 

[taigovinda]

If you have this:

=Sheet1!A1

...then you can change it to this and zeroes will change to empty text strings:

=if(Sheet1!A1="","",Sheet1!A1)

 

[lionpants]

=IF(Sheet1!A1<>"",Sheet1!A1,"")

Where A1 is the cell on the first sheet, and this function would go in the second sheet.

EDIT: Beat me to it. :D

 

[lenze]

How is your workorder set up? It might be better to use code so that if you needed to, you could change the Invoice sheet without messing up the formula.

lenze

 

[zteam777]

its not working

here are the actual cell numbers

AW4 which is the same on sheet 1 as sheet 2.

 

[lenze]

Something like this in the Sheet1 module

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Count > 1 Then Exit Sub
 Select Case Target.Address
 Case "$A$4", "$A$5": Sheet2.Cells(Target.Row, Target.Column) = Target
 Case Else:
 End Select
End Sub

Add cells as needed. This assumes the cells re in the same location on each sheet. If not, it can be modified for your exact needs.
lenze

 

[zteam777]

Dont need to change the code because the invoice is never meant to be typed in therefore the formula will not be touched.

I just need the formula to enter correctly.

Thanks

 

[lionpants]

=IF(Sheet1!AW4<>"",Sheet1!AW4,"")

This doesn't work? Put it in AW4 on Sheet2.

If your sheets are not named Sheet1 and Sheet2 and say they were named Bunnies and Turtles, it would look like this...

=IF('Bunnies'!AW4<>"",'Bunnies'!AW4,"")

It would go in AW4 on the Turtles sheet.

 

[zteam777]

Sorry people, I am still doing this wrong.

ok, before all I did was select AW4 from sheet 2 and type = then went to sheet 1 and selected AW4 and entered. It worked fine except for the 0 being displayed.

When I go to look at the formula on sheet 2 and how it reads, it reads like this:

='Work Order'!AW4:BH6

I had merged AW4 thru BH6 to make one cell earlier.

I still need help on this one.

 

[taigovinda]

So this isn't working?

=if('Work Order'!AW4="","",'Work Order'!AW4)