Invoice Number Tracking Question

[B_2]

Hello Again

A question for the Gifted..

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

 

[Airwolf]

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

 

[K_West]

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

 

[Corticus]

Hi!

I set up a sheet as follows, I needed Column E, but you could hide it:

Book3
	A	B	C	D	E
1
2
3	Paid:		Account	Paid?
4	65465		16546547	Y	16546547
5	4874564		5679865	Y	5679865
6	5679865		69486	N
7	16546547		846346	N
8	45622837		65465465	N
9	78542238		45622837	Y	45622837
10	82824564		54923269	N
11	84129175		64223700	N
12	92124995		73524132	N
13			82824564	Y	82824564
14			92124995	Y	92124995
15			4874564	Y	4874564
16			78542238	Y	78542238
17			84129175	Y	84129175
18			89716112	N
19			95303049	N
20			65465	Y	65465
21				N
Sheet1

Put the following code on sheet1:

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
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select

Application.EnableEvents = True

End Sub

That should do the trick!

HTH,

 

[royUK]

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

 

[kskinne]

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

 

[Corticus]

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,