Hi,
I am needing help with a formula to determine the latest invoice number based on two columns of information.
Column A - Invoice Number - Simply a sequential list of numbers.
Column B - The "TS (timesheet) Period Ending Date" - contains a VLookup formula that looks up the invoice number in column A. This column also contains #N/A errors that must remain so that the information is not plotted on a chart.
In cell A1, I would like a formula to report the maximum invoice number where column B contains a date (i.e., not a #N/A error). So, in the example below, I want the formula to report a "9" in cell A1.
A B
1 29 Mar 2015
2 INVOICE TS PERIOD
NUMBER ENDING DATE
3 1 9 Nov 2014
4 2 23 Nov 2014
5 3 7 Dec 2014
6 4 28 Dec 2014
7 5 11 Jan 2015
8 6 1 Feb 2015
9 7 15 Feb 2015
10 8 8 Mar 2015
11 9 29 Mar 2015
12 10 #N/A
13 11 #N/A
Any help anyone can offer would be greatly appreciated.
Thanks!
Pam
I am needing help with a formula to determine the latest invoice number based on two columns of information.
Column A - Invoice Number - Simply a sequential list of numbers.
Column B - The "TS (timesheet) Period Ending Date" - contains a VLookup formula that looks up the invoice number in column A. This column also contains #N/A errors that must remain so that the information is not plotted on a chart.
In cell A1, I would like a formula to report the maximum invoice number where column B contains a date (i.e., not a #N/A error). So, in the example below, I want the formula to report a "9" in cell A1.
A B
1 29 Mar 2015
2 INVOICE TS PERIOD
NUMBER ENDING DATE
3 1 9 Nov 2014
4 2 23 Nov 2014
5 3 7 Dec 2014
6 4 28 Dec 2014
7 5 11 Jan 2015
8 6 1 Feb 2015
9 7 15 Feb 2015
10 8 8 Mar 2015
11 9 29 Mar 2015
12 10 #N/A
13 11 #N/A
Any help anyone can offer would be greatly appreciated.
Thanks!
Pam