Page numbering in Excel with formula

darzu

New Member
Joined
Jul 13, 2020
Messages
9
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
I need a solution in Excel, i want to make an indexing page (a sort of content page / summary with page numbering) i want to use a formula what will search for a specific word eg. (OEM A & OEM B) and will make a summary page as i attached in the picture.
 

Attachments

  • EvPW5.png
    EvPW5.png
    8.3 KB · Views: 12

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Here's how I would do it. The page numbers in column B are just numbers, 1 to 5 with a custom cell format of "Page "0
Book1
AB
1OEM APage 1
2OEM A
3OEM A
4OEM A
5OEM A
6OEM BPage 2
7OEM B
8OEM B
9OEM B
10OEM B
11OEM BPage 3
12OEM B
13OEM B
14OEM B
15OEM B
16OEM BPage 4
17OEM B
18OEM B
19OEM B
20OEM B
21OEM BPage 5
22OEM B
23OEM B
24OEM B
25OEM B
26
27
28OEM APage 1 / Page 1
29OEM BPage 2 / Page 5
Sheet8
Cell Formulas
RangeFormula
B28:B29B28="Page "&MINIFS($B$1:$B$25,$A$1:$A$25,A28)&" / Page "&MAXIFS($B$1:$B$25,$A$1:$A$25,A28)
 
Upvote 0
Hello,

unfortunately i am using office 2016 and i don't got the MINIFS function and on my business computer we are using only 2016, sorry that i didn't specified this, also i would like to specify an important statement the column B it is not existing the Page numbering, i created the Page numbering only to demonstrate the page separations.

Thanks
 
Upvote 0
Your example shows 5 identical items per page, will your actual file have a similar layout or could the number of lines per page vary?
Will it always be whole pages of items, or could they be mixed? If mixed, how should that be reflected in the page numbers?
Will column A always be sorted in ascending order as per the example?
 
Upvote 0
Hello,
there is not a standard how many rows will be per page (it is defined based on the print area) the number per page may vary.
Yes can be on the same page 2 different item and on the summary page it should show me exact the same thing the 2 items starts and ends on the same page

I tried to create an example on the last statement:

OEM APage 1
OEM A
OEM A
OEM B
OEM B
OEM APage 1 - Page 1
OEM BPage 1 - Page 1
 
Upvote 0
If print area is the only known factor for the start and end of each page then, to the best of my knowledge, what you are asking will not be possible with a formula.
 
Upvote 0
If print area is the only known factor for the start and end of each page then, to the best of my knowledge, what you are asking will not be possible with a formula.
Ok in understand, lets say i make an aditional column and complete on each row the page numbering it should work in this way?

OEM A1
OEM A1
OEM A1
OEM B1
OEM B1
OEM APage 1 - Page 1
OEM BPage 1 - Page 1
 
Upvote 0
Yes, that would work as long as you do each row as in your last example, with the rows merged as in the screen capture in post 1 would not work).

Will column A always be sorted in ascending order? If it will then you can use this method, for descending or mixed order some changes will be needed.
Book1
AB
1OEM A1
2OEM A1
3OEM A1
4OEM A1
5OEM A1
6OEM B2
7OEM B2
8OEM B2
9OEM B2
10OEM B2
11OEM B3
12OEM B3
13OEM B3
14OEM B3
15OEM B3
16OEM B4
17OEM B4
18OEM B4
19OEM B4
20OEM B4
21OEM B5
22OEM B5
23OEM B5
24OEM C5
25OEM C5
26
27
28OEM APage 1 - Page 1
29OEM BPage 2 - Page 5
30OEM CPage 5 - Page 5
Sheet8
Cell Formulas
RangeFormula
B28:B30B28="Page "&INDEX($B$1:$B$25,MATCH(A28,$A$1:$A$25,0))&" - Page "&INDEX($B$1:$B$25,MATCH(A28,$A$1:$A$25,1))
 
Upvote 0
Well the OEM name is not sortable automatically but it should be grouped manually by my colleagues.

I will try to implement the above solution and will get back with feedback.

Thanks
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top