Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to get a Report Header from a cell in a worksheet 7
- Thread starter cboz
- Start date
-
3
- #2
Try the Microsoft KnowledgeBase:
-Larry
-Larry
-
4
- #3
DaleWatson123321
Programmer
cboz,
The Microsoft KnowledgeBase link reference by Larry is indeed a good suggestion.
Having reviewed this link, I want to add my 2 cents worth regarding a "significant omission" with this example, as with most other Microsoft examples. Microsoft has had a "longstanding" practice of providing examples that do NOT emphasize the significant importance of using "ranges names".
As a result of this "bad practice", there are MANY Excel users who are placed at a "significant" disadvantage. From having worked with computer course graduates who didn't even know that "range names" existed in Excel - never mind their significant importance, I am deeply concerned that Microsoft needs to make a change with regard to their examples, and start placing "emphasis" on the significance of using "range names".
Creating and using range names is VERY important for the following reasons:
1) In the example Microsoft provides in this case of picking up the contents of a particular cell and using that value in a Header or Footer, the example uses VBA. Whenever a "cell reference" is used in VBA (e.g. "A1"
, the term used for that type of code is "hard coding". Hard-coding means that it is code that will ONLY change when the VBA code is changed.
Now imagine for a moment that you've created a reasonable sized Excel application, where you've used this "hard coding" to refer to various cells spread throughout the worksheet and/or on different worksheets.
Next, appreciate what type of "nightmarish" task you'll have when your boss asks you to make significant changes to your application, requiring inserting of columns and rows, and moving data from place to place. In such a situation, you will of course be forced to go through all your code and make changes to each of those references to "cell coordinates" - e.g. "A1", "J2", etc.
If, on the other hand, you had created and used "range names" in your VBA code, then there would be NO changes required. The reason is because "internally", Excel maintains a "link" between the "ranges names" you create and the cell coordinates. Therefore, whenever you insert rows or columns or move data, the "range names" automatically get changed.
2) By using ranges names, you can easily make your application "user friendly" - both for yourself and for others who will be using your application. For example, to go to a particular location, all one has to do, is:
a) Hit the "GoTo" key - the <F5> function key,
b) Type the name, and hit <Enter>
So the obvious practice to use, is to assign range names that are easily "associated" with the type of data residing at those particular locations. For example, if you want to go to a location that contains "revenues", you could use a range name of "revenues", or an abbreviation of "rev".
3) The user of range names is an easy way of referencing cells in a formula, where you want to have the reference in the formula as an "absolute" reference - i.e. it will NOT change when the formula is copied to another location.
Naturally the "verbal" range name assigned is also FAR easier to reference and understand, whether you are just creating a formula, or looking at the formula a year later.
4) By using range names, it becomes FAR easier to place data on SEPARATE worksheets, and still be able to reference the data in those cells by referencing the ranges names. And having some data on a SEPARATE sheet - out of the way - can be advantageous.
You only have to look at this same Header/Footer example - of picking up the value in cell "A1" as in Microsoft's example (or "J2" as in cboz's example). If a value is placed on the same worksheet as is being printed, then this could potentially cause a problem related to printing the contents of the cell "twice" - once in the cell, and once in the Header or Footer.
There are "other" situations where the data you want to include in a report on one worksheet, needs to "linked" to cells on another worksheet. Again, this is made FAR easier with the use of range names.
METHOD OF CREATING RANGE NAMES...
While there are different ways to create range names, the method I always recommend, is:
a) Highlight the cell or range-of-cells
b) Hold down the <Control> key, and hit the <F3> key
c) Type the name
d) Hit <Enter>
Well... maybe that was at least 3 cents worth But I hope you at least find this information informative and useful.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
The Microsoft KnowledgeBase link reference by Larry is indeed a good suggestion.
Having reviewed this link, I want to add my 2 cents worth regarding a "significant omission" with this example, as with most other Microsoft examples. Microsoft has had a "longstanding" practice of providing examples that do NOT emphasize the significant importance of using "ranges names".
As a result of this "bad practice", there are MANY Excel users who are placed at a "significant" disadvantage. From having worked with computer course graduates who didn't even know that "range names" existed in Excel - never mind their significant importance, I am deeply concerned that Microsoft needs to make a change with regard to their examples, and start placing "emphasis" on the significance of using "range names".
Creating and using range names is VERY important for the following reasons:
1) In the example Microsoft provides in this case of picking up the contents of a particular cell and using that value in a Header or Footer, the example uses VBA. Whenever a "cell reference" is used in VBA (e.g. "A1"
, the term used for that type of code is "hard coding". Hard-coding means that it is code that will ONLY change when the VBA code is changed.Now imagine for a moment that you've created a reasonable sized Excel application, where you've used this "hard coding" to refer to various cells spread throughout the worksheet and/or on different worksheets.
Next, appreciate what type of "nightmarish" task you'll have when your boss asks you to make significant changes to your application, requiring inserting of columns and rows, and moving data from place to place. In such a situation, you will of course be forced to go through all your code and make changes to each of those references to "cell coordinates" - e.g. "A1", "J2", etc.
If, on the other hand, you had created and used "range names" in your VBA code, then there would be NO changes required. The reason is because "internally", Excel maintains a "link" between the "ranges names" you create and the cell coordinates. Therefore, whenever you insert rows or columns or move data, the "range names" automatically get changed.
2) By using ranges names, you can easily make your application "user friendly" - both for yourself and for others who will be using your application. For example, to go to a particular location, all one has to do, is:
a) Hit the "GoTo" key - the <F5> function key,
b) Type the name, and hit <Enter>
So the obvious practice to use, is to assign range names that are easily "associated" with the type of data residing at those particular locations. For example, if you want to go to a location that contains "revenues", you could use a range name of "revenues", or an abbreviation of "rev".
3) The user of range names is an easy way of referencing cells in a formula, where you want to have the reference in the formula as an "absolute" reference - i.e. it will NOT change when the formula is copied to another location.
Naturally the "verbal" range name assigned is also FAR easier to reference and understand, whether you are just creating a formula, or looking at the formula a year later.
4) By using range names, it becomes FAR easier to place data on SEPARATE worksheets, and still be able to reference the data in those cells by referencing the ranges names. And having some data on a SEPARATE sheet - out of the way - can be advantageous.
You only have to look at this same Header/Footer example - of picking up the value in cell "A1" as in Microsoft's example (or "J2" as in cboz's example). If a value is placed on the same worksheet as is being printed, then this could potentially cause a problem related to printing the contents of the cell "twice" - once in the cell, and once in the Header or Footer.
There are "other" situations where the data you want to include in a report on one worksheet, needs to "linked" to cells on another worksheet. Again, this is made FAR easier with the use of range names.
METHOD OF CREATING RANGE NAMES...
While there are different ways to create range names, the method I always recommend, is:
a) Highlight the cell or range-of-cells
b) Hold down the <Control> key, and hit the <F3> key
c) Type the name
d) Hit <Enter>
Well... maybe that was at least 3 cents worth
But I hope you at least find this information informative and useful.Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Thread starter
- #4
Thank you for solving my problem. I do use named ranges in some cases, however, in ths instance "J2" is always the cell that is referenced. I agree that the use of named ranges is a well kept secret. I had probably created hundreds of spreadsheets before I discoved them. I usually only use them when the spreadsheet formulas that get so involved that I can't make sense of the cell references. A bad habit on my part. Thanks for directing me to the MS support site. In future I will check there first.
DaleWatson123321
Programmer
cboz,
Thanks for sharing your experience, and confirming my suspicion - that there appears to be many Excel users who unfortunately have not been utilizing range names.
I feel Larry deserves a STAR for suggesting the Microsoft Website, so please allow me to award him one.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Thanks for sharing your experience, and confirming my suspicion - that there appears to be many Excel users who unfortunately have not been utilizing range names.
I feel Larry deserves a STAR for suggesting the Microsoft Website, so please allow me to award him one.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question