Jump to content


Photo

Using a counter that automatically resets

INT OFFSET MOD COUNTA tables structured references

  • Please log in to reply
4 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 16 February 2016 - 04:51 PM

I want to create a counter in a helper column on a worksheet called Counter. The starting value of this counter will be 1 and will increase by 1 as the rows go down until it reaches the number of products listed on the Products tab.
 
So if I have 24 products, the counter will start at 1 on the first row on Counter, then increase to 2 on the next row, and so on.
 
When the counter reaches the total number of products on the Products tab, I want it to start over again at 1 on the next row and repeat the process of counting up to the total number of products.
 
The purpose of this counter is to be used as a reference in other formulas to help me extract the name of the product using VLOOKUP (I haven't included these formulas in the attached file because I only want to focus on the counter for now). The reason I'm avoiding using a simpler method is because I want this counter to be dynamic. So whenever I update the list of products, the counter should automatically detect how many of them there are, therefore, the other VLOOKUPs I'll have in other cells will know where to go for their values and know when to start/stop when the counter reaches its maximum.
 
Attached File  counter example.xlsx   13.74KB   70 downloads


#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 17 February 2016 - 07:48 PM

Hi phoenixrizing,

 

I think you can achieve this by using the MOD function. MOD returns the remainder after dividing, so it will allow you to achieve what you need.

 

You can see more about how the MOD function works in the video tutorial: Expert Skills Lesson 1-7 Apply an advanced filter with function-driven criteria.

 

It will also be easier to count the number of products if you convert the ranges into tables. This will allow you to use the structured reference formula:

=COUNTA(Table1[Products])

 

Tables are explained in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

For more on structured references, see: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

 

I have attached a copy of your workbook with the new MOD function applied:

 

Attached File  counter example.xlsx   14.37KB   72 downloads

counter example.png

 

I hope this is what you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 18 February 2016 - 05:50 PM

Jonathan, this is fantastic! Thank you. :)

 

When I implemented the code into my master spreadsheet, it worked but then I discovered I lacked another helper column to help with identifying the number of rows to use in my offset to pull in the desired value from another sheet.
 
I only think I need a helper column, so if a formula is more efficient, I'm open to that.
 
Anyway, here's what I am trying to do:
  1. fill column C with the first item in the Values list, which is "item 1".
  2. keep showing "item 1" down the rows in column C until you get to the next 1 in Mcount.
  3. When you get there, increase Rcount by 1.
  4. Repeat the steps above until all cells in column C are filled with as many values in the Values list are required.
Rcount is the value I'm using as the number of rows to go down in my Offset formula (which is not in this file at the moment). I just want to fix this Rcount counter.
 
I thought it might help if I determine how many of the maximum number of products (on the Products tab) are in Mcount. While that's easy enough to do with a COUNTIF, I couldn't figure out a way to use that figure in my attempts.
 
I entered comments in cells in Column C to help explain what I want to happen at those points.
 
Thanks in advance for your help, Jonathan.
 
 

Attached File  counter example_jonathan.xlsx   29.46KB   58 downloads

 



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 18 February 2016 - 07:13 PM

HI phoenixrizing,

 

It seems like your RCount column could be calculated in a similar way to your MCounter column but instead of using MOD, just dividing by the number of products and using the INT function to return a whole number, like this:

=INT([@Count]/Products!$D$1)

 

You can then use the resulting numbers along with the OFFSET function to get the values you want, like this:

=OFFSET($M$3,[@Rcount],0)

 

You can see more about how to use the OFFSET function in the video tutorial: Expert Skills Lesson 4-6 Create dynamic formula-based range names using the OFFSET function.

For more on the INT function, see the video lesson: Expert Skills Lesson 3-9 Use the Excel DATEDIF function and YEARFRAC function.

 

I have attached a copy of the workbook with the new formulas applied:

 

Attached File  counter example_jonathan.xlsx   29.25KB   50 downloads

counter example_jonathan.png

 

I hope this is the solution you were looking for, but please feel free to reply again if you're still not sure of anything.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 23 February 2016 - 03:13 PM

Thanks, Jonathan. I continue to learn so much from you.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users