EXCEL Formula Simple Problem

zteam777

New Member
Joined
Nov 15, 2009
Messages
11
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
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

Board Regular
Joined
Nov 11, 2009
Messages
117
Code:
=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
 
Last edited:

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Nov 15, 2009
Messages
11

ADVERTISEMENT

its not working

here are the actual cell numbers

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

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Something like this in the Sheet1 module
Code:
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

New Member
Joined
Nov 15, 2009
Messages
11

ADVERTISEMENT

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

Board Regular
Joined
Nov 11, 2009
Messages
117
Code:
=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...

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

It would go in AW4 on the Turtles sheet.

 
Last edited:

zteam777

New Member
Joined
Nov 15, 2009
Messages
11
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,979
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top