writing code off of column headings?

Hi Kenman,

Yes, I was amazed to discover this Excel capability about a year ago even though I have been an avid Excel user for many years. Once you have named two ranges that intersect, you can get the cell (or top-left cell if multiple cells) of intersection by simply giving the names separated by a space. So, per your example, say you have columns A:G named Monday ... Sunday, and you name the rows Week1, Week2, ... , Week52. Then a formula that will give the value of the cell at the intersection of Week 38 and and Tuesday would simply be

=Tuesday Week38

or

=Week38 Tuesday

It is also quite easy to write a macro that automates naming of the rows and columns based on row and column headings. Would you like code for this?

Damon

Hi,

FYI
you don't need to name the ranges
you can use the labels itself
therefore you need to check "accept labels in formulas"
menu Tool/Options/Calculate-Tab
  A     B   C   D   E   F   G  H  
1       Mon Tue Wed Thu Fri       
2 week1 B2  C2  D2  E2  F2     B4 
3 week2 B3  C3  D3  E3  F3     E2 
4 week3 B4  C4  D4  E4  F4        
5 week4 B5  C5  D5  E5  F5        
6 week5 B6  C6  D6  E6  F6        
7 week6 B7  C7  D7  E7  F7        
8 week7 B8  C8  D8  E8  F8        

Blad1
[Table-It] version 05 by Erik Van Geit
kind regards,
Erik

Wow. Thanks Erik. I learned something else new. And I thought I was too old...

Damon

This makes sense, but I never knew it. I guess Microsoft's description in the version changes was written poorly or something?
In any case this opens a whole lot of posible applications up. I will use it to make formulas more universal and dynamic. Thanks.

Eric, Damon, and Joe,
Thanks for the replies..!! A couple of questions for all of you. Where do you find out these type of things. Books, school, playing with the program? I can't imagine the things that could be done with this and any thoughtfully written program that are unknown to the casual user.
Are all of you professional programmers or is this just a hobby? I've always wanted to get into the programming end of it as a job. What or how would you recommend doing this? Does Mr. Excel pay you for responses? What a great website...!!!! and what usefl information you all provide. Thanks again. Damon, if you would like to share your VBA programming for the above question, I don't know if I need it, but it wouldn't hurt to have it available if I do.

Kenman

Damon & Joe,

It's new for me to; just discovered this a few weeks ago...

kenman,

Does Mr. Excel pay you for responses?

Click to expand...

No, but it's kinda deal. You can imagine some people are looking for Excel consultants on this forum Also we are learning new stuff from others.
Great part of my Excelknowledge comes from here.
For me it's a hobby-profession just like my music. It's difficult for me to feel the difference. A lot of people are posting here during their working time at the office. Every minute I spend here is free-time at home.

As for tips on books, I'll let this to the others, never used any book. (perhaps I should)

best regards,
Erik

I have been programming for about 42 years, started with analog computers and the programmed compiler [like a old switchboard, with banana plugs]. Have done most languages, most of the time did mainframe. Have done binary core and kernel and read binary.

Have not found books too helpful, generally too basic. This site is great, the best of the lot. When I teach, I have people build something they will use. This is the best way to learn. An overview of all the features of a product is just too overwhelming, learn the things you will use first.

Copy code and play with it adding features and building more into it. Change the code to see what it will do. Learn to think like the product.

Basic code ideas are used everywhere, the details are different. Most product documentation is poor and incomplete, you really need to play with it yourself. I learn new things all the time. The hardest thing for me was to learn object. This type of programming requires a leap of fath in how the object was built and it takes away some of the control you have, you must think like the programmer who wrote the object and that is not easy to do.

No, we do not get paid, but there are perks. Like I get most of the beta versions of products for fee directly from the manufactures to test and help improve. So much of the software I use I never had to pay for.

Plus this board is like a network and we clue each other into deals.

A bit more on using the labels in formulas option and intersect operator.

Not quite sure what you mean. A couple of other excel mvps have answered this. Could you explain your post.