Question

Excel: page numbers in a cell

Asked by: emilio

I would like to find a way to write a reference to a page number in a cell of a sheet.
If I have a value in cell number A1 at sheet1, the page number of this will probably be 1, and for example, I would like to have at sheet2!B3 the value of the page number of sheet1!A1
Is there any macro, formula or whatever to get it?

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
1999-04-14 at 01:51:41ID10150097
Tags

cell

,

number

,

page

Topic

Microsoft Office Suite

Participating Experts
6
Points
800
Comments
27

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. cell a1
    I had put command botton and 1 text box in excell worksheet. and in command botton code is "Sheet1.Columns(a1, a1) = TextBox1.Text 'Sheet1!b2 = TextBox2.Text" why it fail ? i just one when the time i write word "Hujan" in the text box 1 , that word will ...
  2. copy cell formula in Excel Macro
    I have a macro that copy information from a group of cells to another group of cells. It seems though that when I do the copy, Excel grabs the VALUE of the cell and not the FORMULA. I can understand why, the cells being copied to might not have the same cell numbers and wha...
  3. Problems inserting a formula in a cell using VBA
    Hi All, I write this forum, trying to find a solution for a little problem I have with my Excel spreadsheet. I'm tryiing to insert a (not very) complex formula in a cell using the ".Formula" property of the cell, but it fails with a 1004 run-time error. When inse...
  4. An apostrophe in a cell A1 will not show up in the VBA ran…
    If I have '=a2 in cell A1 and then run the following code Sub test() dim sFormula as string sFormula = Range("a1").Formula End Sub sFormula will be set to =a2 how can sFormula set to '=a2 ??? Thanks
  5. I want sheet2 cell A1 to always refer to sheet1 cell a1 …
    I hope this is not confusing. I have multiple sheets - sheet2 refers to sheet1. For example sheet2 cell a1 equals the value of sheet1 cell a1, but if I insert a new row on sheet1 above the current row1 (I have a new cell a1) I still want sheet2 cell a1 to equal the value of...
  6. How can I rename sheet1 to "John" the cell A1 will cha…
    for example, I have sheet1 and cell A1, when I rename sheet1 to John the cell A1 will change to John. or If I put text John in cell A1, sheet1 will change to John.

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: MarkWPosted on 1999-04-14 at 02:30:07ID: 1619277

About using formulas to calculate values on other worksheets and workbooks

You can share data stored on different worksheets and workbooks by using linking, or external references. Linking is especially useful when it is not practical to keep large worksheet models together in the same workbook. Linking is not limited to links between two workbooks; you can also construct hierarchies of linked workbooks. In the following example, the AVERAGE worksheet function calculates the average value for the range B1:B10 on the worksheet named Marketing.


You can use external references to:

·      Merge data from several workbooks. You can link workbooks from several users or departments and then integrate the pertinent data into a summary workbook. The original workbooks can still be changed separately from the summary workbook.
·      Create different views of your data. You can enter all of your data and formulas into one or more source workbooks and then create a report workbook that contains links only to the pertinent data in the source workbooks.

·      Streamline large, complex models. By breaking down a complicated model into a series of interdependent workbooks, you can work on the model without opening all of its related sheets. Smaller workbooks are easier to change, don't require as much memory, and are faster to open, save, and calculate.

 

by: bruintjePosted on 1999-04-14 at 02:31:45ID: 1619278

Hi Emilio,

I probably miss something,

But you can copy the cell A1 in sheet1
and right click in sheet2 on the cell you want
then choose paste special
and choose paste as a reference the bottom right button

now in the cell should be something like

=Sheet1!$A$1

Let me know if you needed something else
:O)Bruintje

 

by: sduckettPosted on 1999-04-14 at 03:39:37ID: 1619279

MARKW:
This really is a bad answer! Not only that, it is a straight copy and paste from the Excel Help-files!
Read the question and see that you have not even chosen a very relevant help-file.
Here's an interesting URL that will let you know my feelings about people who short-change askers who need GOOD advice, http://www.experts-exchange.com/jsp/qShow.jsp?ta=commspt&qid=10120219 .
This site is not a scramble for points, although, in my opinion, too many people treat it like that. :0|

emilio: Reject this proposed answer and let somebody have an honest attempt at giving you your solution. Also, bear in mind that paying out 800 points for a question is excessive and is only likely to attract point-grabbers. :0)

 

by: sduckettPosted on 1999-04-14 at 03:49:22ID: 1619280

MarkW:
I notice you did exactly the same trick at http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10147893 , where other REAL experts had put time and effort into solving somebody's problem only for you to come along and steal the points with a very general answer pasted from Help. Can you really program the creation and destruction of Toolbars?

 

by: sduckettPosted on 1999-04-14 at 05:15:28ID: 1619281

emilio try this:

Function PageNum(c As Range) As Long
Dim cRow As Long
Dim ws As Worksheet
Dim x As Long
cRow = c.Row
Set ws = c.Worksheet
For x = 1 To ws.HPageBreaks.Count
    If ws.HPageBreaks(x).Location.Row > cRow Then
        Exit For
    End If
Next
PageNum = x
End Function

It seems to work MOST of the time and I am just trying to work out why it doesn't work ALL of the time. It does work for your example though. (In Sheet2!B3, type "=PageNum(Sheet1!A1)")

 

by: sduckettPosted on 1999-04-14 at 05:25:37ID: 1619282

Also, it only works for horizontal page-breaks. Introducing vertical page-breaks complicates the matter somewhat. Watch this space...

 

by: reddarinPosted on 1999-04-14 at 06:32:09ID: 1619283

Wow, 800 points! Mark are you related to ViCtOrNeT?

 

by: sduckettPosted on 1999-04-14 at 08:23:29ID: 1619284

Darin:
Why do you ask that?

 

by: reddarinPosted on 1999-04-14 at 09:57:12ID: 1619285

There was a question posted for 1000 (a full grand) points that Victor answered in the lamest way possible. (Not saying that Mark's answer approach that level of lame).

The question had to do with creating an app that would automate the process of burning files to CDRs. Victors answer ran along the lines of '...there is some API commmand called fFileBurnCD or something and you can problably use that. Post another comment if you need more help...'

Pretty darn lame.

 

by: bruintjePosted on 1999-04-14 at 11:58:40ID: 1619286

sduckett > I'm a point-grabber..:O).......and I'm not attracted 800 points is way to much I agree,

but back to the problem, I didn't understood it correct....looking at your post.....

 

by: reddarinPosted on 1999-04-14 at 12:39:15ID: 1619287

Man, there must be grimlins at EE. I swear I carefully read my post before I hit submit and after it is submitted letters seem to fall off.

>...answer approach that level of lame

was (or should have been)

>...answer approaches that level of lame

 

by: bruintjePosted on 1999-04-14 at 13:45:07ID: 1619288

we weren't disturbed about it, don't worry :O)

 

by: MarkWPosted on 1999-04-14 at 15:49:52ID: 1619289

sduckett does any of this look familiar? I would like to draw you attention to the first part...READ IT SLOW!
Knowing where to find the answer is a large part of be knowledgeable.  No one can store all the information available in their brain.  

The purpose of the site is to provide an answer to the questions posted.  If the answer can be copied from help and cut and pasted to the question, then the question has been answered.  The customer can always post comments requesting more information.  Or if the proposed answer does not provide the information he wanted, he can reject the answer.

I think you are probably over-reacting to accuse agrillage of "Plagiarism".  He was only providing the information requested and apparently jtjcomp agreed since he has graded the question and moved it to the previously asked questions.

Linda Gardner
Customer Service @ Experts Exchange

Truth be knowen you looked in the help file first..how else would you know where it came from?  

Keep up the good work sduckett a few more self-righteous flamers like you in this world can’t be all bad!

 

by: MarkWPosted on 1999-04-14 at 15:59:56ID: 1619290

Just in case it doesn't look familiar, let me refreash your memory.
www.experts-exchange.com/jsp/qShow.jsp?ta=commspt&qid=10120219  asked by sduckett to customer service 1-22-99

 

by: reddarinPosted on 1999-04-14 at 16:39:01ID: 1619291

There was nothing self-righteous about his comment.

The question was:
"I would like to find a way to write a reference to a page number in a cell of a sheet.
If I have a value in cell number A1 at sheet1, the page number of this will probably be 1, and for example, I would like to have at sheet2!B3 the value of the page number of sheet1!A1
Is there any macro, formula or whatever to get it?"

And your answer was [paraphased]: Yes you can do that with linking or external references.


So, how did you address the question with your answer? How can this person take your answer and convert it into usable knowledge? They can't. Not without scouring the help files to figure out what Linking and External Refernences are.

Don't give a reply about how the smallest spark can set off the biggest flame. It has been said before.

I'll quote your quote: "If the answer can be copied from help and cut and pasted to the question, then the question has been answered"

Your answer does not qualify as valid per your own quote of that quote since it is impossible to go from where he is now to where he wants to be directly by way of your answer.

Let me put that another way. If I ask 'How do I make fire?' and you answer with 'It is possible to use fire to cook food and provide heat for a more comfortable living environment. You can cause combustion by using combustible materials. Combustible materials are normally found in great abundance and are very useful indeed'. Exactly how have you answered my question? Yes, you did speak of the topic of fires. I didn't ask about fires in general, I asked specifically how to make a fire.

Nothing self-righteous there, is there? Quoting a help file or a KB article is fine by me. I do it all the time. But quoting some generic passage about the sky being blue is not answering the question. If you posted a question for 800 points (or 50 points) wouldn't you expect an answer that was at least directly addressed to the problem you were trying to solve?

Darin

 

by: MarkWPosted on 1999-04-14 at 20:29:21ID: 1619292

At least I offered something...you on the other hand offered nothing except to comment on the comments...good job...EXPERT!

 

by: sduckettPosted on 1999-04-15 at 01:59:41ID: 1619293

MarkW:
I looked in the Help File to search specifically for where you got your text. It took me no time to find it, I typed "External References" in the Office Assistant. I already know all about external references and external references are irrelevant to this Q because, as you can see, IF YOU READ IT SLOWLY, it is really about what page-number a particular cell belongs to.
That is not to say I don't approve of using the help-file, I use it every day. Then I usually compile and test a relevant example and post that. I have even been known to paste directly from Help into a COMMENT but it has always been as part of a larger response, always been mentioned that it was from Help and ALWAYS relevant.
I suppose my PAQ, http://www.experts-exchange.com/jsp/qShow.jsp?ta=commspt&qid=10120219  (I gave you the URL remember), was wrongly titled. My problem is not with people using relevant parts of help, if in context. My problem is with people treating this site like a scramble for points, to the severe degradation of the usefulness of this site and consequently the popularity of the site to people needing help. Please read the whole thread not just Linda's answer, SLOWLY.
MarkW, why don't you tell us exactly why or how you thought your answer would have been of any help. Why did you post it as an Answer not a Comment (an action that would have avoided any comment at all from me). Remember your history goes before you MarkW. So far you have two PAQs in this forum, one of them was pasted from Help (see above for URL) This was where you came along to a question having offered nothing to the preceding discussion, pasted the help-file and claimed the points. The help you pasted was on the subject that people had been already been discussing but you couldn't even be bothered to think of a relevant example using the Help-file you had uncovered.

<good job...EXPERT> These are the only words out of all you have typed that ring true (and I have to take them completely out of context to be able to say that). Reddarin has been helping here with good quality answers for about a year now. He has, indeed, been doing a good job and in his case I think the word expert fits.
bruintje: not a 'point-grabber' but 'competetive', there's a big difference. You have helped many, enormously.

I really can't be bothered to get into a war of words this time. Think what you like. Maybe I am self-righteous? Maybe I have other motivations? One things for sure, answers like yours really p*** me off.

 

by: MarkWPosted on 1999-04-15 at 03:56:53ID: 1619294

Well the customer was satisfied...and that IS what is important...as far as Claim the points...I think I was awarded the points because I gave the customer what they felt was the answer THEY were looking for..you did not. As with your paq if people would read the whole thread they will see that once again this whole thing boils down to sour grapes on your part....

Let us remember it is not what other people think of anyones answer. It is what the customer thinks of the answer. They are the ones that accept or reject...not YOU!

 

by: reddarinPosted on 1999-04-15 at 03:58:50ID: 1619295

>At least I offered something...

That's funny.

reddarin's contribution: emilio, To have Microsoft Excel automatically number the pages of the worksheet, enter the word Auto in the First page number box.

Now can I have the 800 points?

 

by: sduckettPosted on 1999-04-15 at 04:45:44ID: 1619296

I've have said this before in another Q and i will say it again just for you MarkW.
My main interest in this site is to improve my OWN skills. I don't give two cents for the points or my position on the leader board. Here I get presented with problems that my work doesn't involve and so get a broader spectrum of problem solving. I think if you can be bothered to trawl back through many of the older questions in this forum you will see that I don't post unwarranted Answers. If there is the slightest element of doubt about whether my answer FULLY meets the asker's requirements, it is posted as a comment. I guess the reason that many of the people who I have entered into discussion on this matter presume it is sour-grapes is because the questions involved have always been high pointers. Really, that is incidental, and reinforces my point that offering high points encourages unscrupulous answers.
As for the other Q I reference. I guess we will never know, without bringing darrel into this discussion, but my feelings are that darrel accepted your pasted help file because his answer had been dealt with by the thread as a whole with little or no new information coming from your offering. In fact, I think darrel answered his own question and just wanted to close the Q. In it, do I complain about not being awarded points or do I complain about you having not pasted the whole of the Help-file as your answer?
As from now MarkW, I shall not be responding to your lame attempts at justifying yourself, just get your act together.
I think it's time we tried to help emilio...

 

by: antratPosted on 1999-04-16 at 03:31:25ID: 1619297

It's a pity all the above time and effort wasn't used to answer
emilio's Question.

I think we need to keep in mind that there is also a Language
barrier to overcome, and before anyone asks NO I don't have an answer for emilio.


 

by: reddarinPosted on 1999-04-16 at 05:08:17ID: 1619298

Interesting.

antrat, you were probably engrossed in the struggle between good and evil and completely missed the first few post were 2 (two) possible answers were posted and 1 (one) 'other' thing was posted that sparked the debate.


sduckett: Wednesday, April 14 1999 - 07:15AM CDT (1 of 2)
bruintje: Wednesday, April 14 1999 - 04:31AM CDT (2 of 2)

markw: Wednesday, April 14 1999 - 04:30AM CDT (sparky)

So, now we are just waiting for the poster of the question to come back in and give some feedback. Both the posted answer and the answers that were posted were done long before the resulting thread ensued.

 

by: sduckettPosted on 1999-04-16 at 05:37:34ID: 1619299

emilio:
Yes, antrat is right I'm sorry about all that. I have been trying to solve your problem though.
It is not an easy problem to solve.

Do you want the page number of the whole workbook or just the worksheet that the referenced cell is on? eg A1 on Sheet2 is on page 1 if you are only printing Sheet2 but it could be a much higher page number if you are printing the whole workbook.

Does anybody know of an API call to help with this as programming using HPageBreaks, VPageBreaks and PageSetup is quite tough. Besides, pagebreak preview shows you the page numbers, how can we extract that info?

 

by: sduckettPosted on 1999-04-16 at 05:40:48ID: 1619300

Also, if you reject MarkW's answer, I am sure a lot more people will notice your question, improving your chances of success a lot.

 

by: MarkWPosted on 1999-04-16 at 06:01:20ID: 1619301

Reject it emilio give me some peace!!

 

by: TmessPosted on 1999-04-16 at 15:35:42ID: 1619302

sduckett, I got your back on this one. I could see a beginner making this mistake but MarkW has been an expert here at EE for sometime. He should know better.

 

by: sduckettPosted on 1999-05-05 at 05:41:24ID: 1619303

emilio:
We're sorry, please come back.
I will start working on your problem again if you let me know you are there.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...