Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-11-2021, 11:26 PM
Marcia's Avatar
Marcia Marcia is offline Last number issued in an invoice pad Windows 10 Last number issued in an invoice pad Office 2019
Expert
Last number issued in an invoice pad
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Last number issued in an invoice pad

This is related to my previous post.


A data sheet contains a list of invoices issued by several staff. If Ms. Alpha (I3) issued invoices numbered 6387127 to 6387132 (defined name, OR_No.), I would like the formula in K3 to return 6387132 which is the last serial number issued by Alpha from this invoice pad of 6387101 to 6387150. The first number issued during the month was 6387127, manually entered in J3. It is possible that a staff may be in charge of more than one invoice pad.
I do not know how to include in the MAXIFS formula the booklet criteria. I have attached a sample sheet for more details.
Thank you.
Attached Files
File Type: xlsx Maximum number in a series.xlsx (12.6 KB, 11 views)
Reply With Quote
  #2  
Old 08-12-2021, 02:30 AM
p45cal's Avatar
p45cal p45cal is offline Last number issued in an invoice pad Windows 10 Last number issued in an invoice pad Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In your file in cell F3:
Code:
=MINIFS(Table1[OR No.],Table1[OR No.],">=" & FLOOR(Table1[@[OR No.]],50))
copied down gives the smallest number in a batch.
In cell G3:
Code:
=MAXIFS(Table1[OR No.],Table1[OR No.],"<=" & CEILING(Table1[@[OR No.]],50))
copied down gives the largest.
Reply With Quote
  #3  
Old 08-12-2021, 02:53 AM
ArviLaanemets ArviLaanemets is offline Last number issued in an invoice pad Windows 8 Last number issued in an invoice pad Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Does this do it?
Attached Files
File Type: xlsx Maximum number in a series.xlsx (12.1 KB, 10 views)
Reply With Quote
  #4  
Old 08-13-2021, 03:02 AM
Marcia's Avatar
Marcia Marcia is offline Last number issued in an invoice pad Windows 10 Last number issued in an invoice pad Office 2019
Expert
Last number issued in an invoice pad
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Arvi.
The formula that looks for the latest number in a series resides in another sheet. It is not part of the table in sheet1. I only placed the problem (I2:K7) in the same sheet as the data, but outside of the table.
Reply With Quote
  #5  
Old 08-13-2021, 10:56 PM
Marcia's Avatar
Marcia Marcia is offline Last number issued in an invoice pad Windows 10 Last number issued in an invoice pad Office 2019
Expert
Last number issued in an invoice pad
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you p45Cal. I tweaked your formulas to conform with the actual data and report sheets.
Reply With Quote
  #6  
Old 08-14-2021, 04:03 AM
p45cal's Avatar
p45cal p45cal is offline Last number issued in an invoice pad Windows 10 Last number issued in an invoice pad Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

If you need to know the lowest Or. No used in a pad (you didn't ask for it in your narrative but did in your sample file) you may need a tweak to the formula I gave for that to:
=MINIFS(Table1[OR No.],Table1[OR No.],">=" & FLOOR(Table1[@[OR No.]]-0.1,50))
this is to cater for when the Or. No. was an exact multiple of 50.
Reply With Quote
  #7  
Old 08-14-2021, 10:55 PM
Marcia's Avatar
Marcia Marcia is offline Last number issued in an invoice pad Windows 10 Last number issued in an invoice pad Office 2019
Expert
Last number issued in an invoice pad
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by p45cal View Post
=MINIFS(Table1[OR No.],Table1[OR No.],">=" & FLOOR(Table1[@[OR No.]]-0.1,50))
this is to cater for when the Or. No. was an exact multiple of 50.
Could you please check cell k 41 in the attached sheet? One pad ended at 529050 while the next pad started at 529051. The formula should return 529051 as the minimum number issued for pad 529051 to 529100 not 529050. Th same holds true with pad 529101.
Thank you.
Attached Files
File Type: xlsx Maximum number in a series.xlsx (19.1 KB, 5 views)
Reply With Quote
  #8  
Old 08-15-2021, 04:17 AM
p45cal's Avatar
p45cal p45cal is offline Last number issued in an invoice pad Windows 10 Last number issued in an invoice pad Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Well spotted! It's never as easy as you think!
In the attached, it all hinges around the adjustment of the formula in cell F3 to get the first Or no. in a pad.
I've used the same adjustment in cell I3, so that no formula requires helper columns.

As an aside, seeing your small table at N2 (in my attachment) I think you can probably get the information you want more reliably and flexibly by not using a single formula but using a pivot table.

There's a pivot table at cell R3 where you'll see that the first column is created by grouping the Or No.s. Right-click any of the values in the first column of the pivot and choose Group… to see the grouping applied. This table gives you the max and min numbers used for each pad - no formulae.

This next bit you can do yourself; add the Rep field either above or below the Or No. in the Rows area of the pivot fields. When you add it above the Or No. you get data remarkably similar to your table.


The file: Excel file
(It was a bit too big to attach. Click on the Download button once the link opens.)
Reply With Quote
  #9  
Old 08-15-2021, 06:10 AM
Marcia's Avatar
Marcia Marcia is offline Last number issued in an invoice pad Windows 10 Last number issued in an invoice pad Office 2019
Expert
Last number issued in an invoice pad
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Beautiful, thank you. The pivot table would be the excellent solution to the tracking and accounting of official receipts received and issued by the collection officers.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automated excel invoice clearing and saving invoice sunboy Excel 2 09-25-2019 12:27 PM
Add a serial number to an invoice pringle747 Excel Programming 6 02-19-2015 11:19 AM
Auto Generate Invoice Number Word 2013 Jonah245 Word VBA 3 09-04-2014 05:24 PM
Invoice number sync from different location? lbf200n Excel Programming 0 04-10-2013 06:55 PM
Last number issued in an invoice pad Invoice Number Generation mrphilk Excel 2 06-08-2010 12:39 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft