# Invoice Number Tracking Question

#### B_2

##### Board Regular
Hello Again

CELLS C4:C1000 have an invoice number placed in them

CELLS D4:D1000 i manually place either an "Y" or "N" to let me know whether the invoice has been paid.

Is there a formula which will return the Invoice Numbers that have a "N" in column D?

So i can track what invoice numbers to chase up rather than have to go down a column...

Thanks Again

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
try a search on Sumif or Array formulae
It is possible to Sum a column where another col has a certain value. Finding this yourself can be fun and get you on the curve!!!!
Good Luck

The easiest way is to sort your spreadsheet by your column D, if column D holds all of you Y and N's. You can go back and change it to sort by date or invoice number, etc. if needed.

K_West

Hi!

I set up a sheet as follows, I needed Column E, but you could hide it:
Book3
ABCDE
1
2
3Paid:AccountPaid?
46546516546547Y16546547
548745645679865Y5679865
6567986569486N
716546547846346N
84562283765465465N
97854223845622837Y45622837
108282456454923269N
118412917564223700N
129212499573524132N
1382824564Y82824564
1492124995Y92124995
154874564Y4874564
1678542238Y78542238
1784129175Y84129175
1889716112N
1995303049N
2065465Y65465
21N
Sheet1

Put the following code on sheet1:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Range("E4:E1000").Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select

Application.EnableEvents = True

End Sub``````

That should do the trick!

HTH,

You could use Autofilter to display either "Y" or "N" as required

assuming you use the autofilter autofilter to filter out all except the N's, you could also use this formula in an unused cell:

=COUNTIF(\$D\$4:\$D\$1000,"N")

to return the total number of N's that are in your range

when you are done tweaking this, you are going to have one awesome invoice template. keep me posted

thanks,
kevin

Per PM request of B2, here's some further explanation (I don't like to explain things off the thread),

Right click on the tab "sheet1" and click on view code. Cut and paste the code I gave you onto the sheet.

Also, copy and paste the formulas in column E to cover the whole range of data input, I didn't want to put a 1000 row sheet on the post, go figure.

This code will keep from having to do any extra button clicking, just add your invoice numbers, tell it yes/no, and the macro automatically sorts and filters the list.

HTH,

Replies
4
Views
101
Replies
6
Views
519
Replies
4
Views
235
Replies
10
Views
846
Replies
1
Views
359

1,203,318
Messages
6,054,712
Members
444,743
Latest member
johncon60

### 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.

### Which adblocker are you using?

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

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