+ Reply to Thread
Results 1 to 7 of 7

Receipt number

  1. 07-31-2019, 07:28 AM #1
    mtwa1990
    mtwa1990 is offline
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Receipt number

    Im trying to enter a formula into a column so that when data is added to that row an ID number is automatically displayed.

    The ID number consists of
    -A letter which is the 1st letter of our store eg M
    -Period number eg 5
    -2 digit Year e.g 19
    -3 digit receipt number eg 026

    SO altogether it will be M519026

    i have come up with the following however i cant figure out how to turn the receipt number back to 001 when the period number changes.

    Please Login or Register  to view this content.

    hopefully somebody can figure it out, there is probably an even more simple way to do it, if so also please let me know

    Thanks Martin
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-31-2019, 07:33 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,376

    Re: Receipt number

    You could use COUNTIF or COUNTIFS to control the number portion of the ID and format the number section using the TEXT function.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-31-2019, 07:47 AM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,376

    Re: Receipt number

    In O2 copied down (this column can be hidden):

    ="M"&VLOOKUP(G2,'Date Lookup'!$A$2:$B$1969,2,0)&RIGHT(YEAR(G2),2)

    In N2 copied down:

    =O2&TEXT(COUNTIF(O$2:O2,O2),"000")
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 07-31-2019, 08:48 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,376

    Re: Receipt number

    Any feedback? Was the suggestion of any use to you?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  5. 07-31-2019, 09:30 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,376

    Re: Receipt number

    Thanks for the rep, but that wasn't what I meant. I had hoped you might say here whether or not it had worked for you in order to round off the thread.

    Please mark the thread as SOLVED if it has.
    Reply With QuoteRegister To Reply
  6. 07-31-2019, 02:09 PM #6
    mtwa1990
    mtwa1990 is offline
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Office 365
    Posts
    52

    Re: Receipt number

    only just got home to try this out, works perfectly thank you very much for your help
    Martin
    Reply With QuoteRegister To Reply
  7. 07-31-2019, 04:52 PM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,376

    Re: Receipt number

    Great - glad to hear it.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Actual Receipt Date Vs. Original Receipt Date (Compare & Provide Result)
    By Rajeshkumar R in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2018, 07:06 AM
  2. [SOLVED] How to exclude duplicate receipt number amount from the total?
    By mso3 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-14-2017, 09:23 AM
  3. Macros for Receipt Log
    By mdasifiqbal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-16-2013, 12:04 PM
  4. Daily Receipt Log
    By mdasifiqbal in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 02-09-2013, 09:26 AM
  5. Daily Receipt Log
    By mdasifiqbal in forum Excel General
    Replies: 0
    Last Post: 02-09-2013, 09:15 AM
  6. Solution to find receipt number on consumption
    By JDVBabu in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-18-2012, 10:15 AM
  7. receipt template
    By MLP in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-31-2005, 11:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1