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.
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.
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.
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.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
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.
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
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-exchang
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)
MarkW:
I notice you did exactly the same trick at http://www.experts-exchang
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
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)")
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.
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!
Just in case it doesn't look familiar, let me refreash your memory.
www.experts-exchange.com/j
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
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-exchang
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.
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!
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...
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.
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?
Business Accounts
Answer for Membership
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.