writing code off of column headings?

[kenman]

Hi All,

I am downloading spreadsheets with similar data but there are some that have more info (columns of data) than others. Because there are so many files it would take me forever (not really but it would sure seem like it) to perform the computations that I am looking to do in each file.
I am trying to see if there is anyway to name a column based on its heading, and then call a specific cell (or group of cells in the column) to perform a function on it. Is this possible?
I.e.and this is a very simple example:
Column headings are Mon, Tues, Wed, Thurs, Fri. in some files and other files also have columns with Sat and Sun. Let's say there are 52 rows of data for a year covering each week.
Now let's say I want to get what is in the Monday column for week 30. Is there anyway I can name the cell that corresponds to that cell using the "Monday" heading on the spreadsheet and get what is in a specific row for each column? Or is there anyway to get the average for 4 rows of data in the Tuesday column for a specific time period?

I hope someone understands the question. Thanks in advance for any replies to this.

Kenman

 

[Damon Ostrander]

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

 

[erik.van.geit]

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

RANGE FORMULA (1st cell)
H2    =Mon week3
H3    =Thu week1

[Table-It] version 05 by Erik Van Geit

kind regards,
Erik

 

[Damon Ostrander]

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

Damon

 

[Joe Was]

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.

 

[kenman]

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

 

[erik.van.geit]

Damon & Joe,

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

kenman,

Does Mr. Excel pay you for responses?

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

 

[Joe Was]

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.

 

[Greg Truby]

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

 

[kenman]

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