How do I mix if with then referance cells

Not sure how clear your question is, but perhaps

=IF('Sheet1'!$D4="Yes",'Sheet1'!E4,"")

=IF('Sheet1'!$D4="Yes",'Sheet1'!A4,"") copied acoss 2 more cells.

First of all I made a mistake, it should be "if no". What I am trying to do is to have excel make a list on sheet2 of the invoices on sheet1 that are marked "no" for not paid.

Sheet1 contains invoices. Sheet2 will be my outstanding account list.

Sheet1 is set -up as follows:
A4 Date B4 Invoice# C4Amount D4 Paid-Y/N E4 Customer Name.

On Sheet2 which will be a list of oustanding Invoices.

So, on Sheet2 A3 I need a formula that will produce Customer Name; Date; Invoice#, and Amount, if Sheet1 D4 is "no".

I need a formula on Sheet2 A3 that sais If Sheet1 D4 is "no" then Referance Sheet1 E4,A4,B4,C4.

In other words list all customers with their relative information that are marked no for not paid on sheet1 columD.

Perhaps there is a better way? I just want a liston Sheet2 that contains the Customer Names; Dates; Invoice #'s, and amounts, of the invoices on Sheet1 that are marked "no" in colum D

Your explanation leads, me anyways, to believe that you are only concerned with row 4.. which apparently you are not, it seems...

So, first off let's add a helper column to your Sheet1.

In a free column, say column F, enter in F2 (assuming the list starts at row 2), =IF(D2="no",COUNT(D$1:D1)+1,"") and copy down. This identifies sequentially the matches found

In another column enter formula =MAX(F:F) to get the last number.

Then in Sheet2, A3 enter formula:

=IF(Rows($A$1:$A1)>'Sheet1'!$G$2,"",INDEX('Sheet1'!E:E,MATCH(ROWS($A$1:$A1),'
Sheet1'!$F:$F)))

wher Sheet1!G2 contains the MAX() formula...

Then copy this formula down.

You similar formula in next column indexing Sheet!A:A, then copy across 2 more columns and down to get the A, B, C column information.