I have a table, and I want to name the cells base on information in the column and the row. For example, the in the table below, I would like the second cell in the second row to be named MilkCarb because of the contents of the left column and the top row. Is there a way to automatically name all the cells in a table like this?
Food Carb Protein Fat Kcal
Milk 12 8 0 90
Veg 5 2 0 25
Fruit 15 0 0 60
Where would this name be stored? I’m no expert at Excel, but there is a CONCATENATE function to add the text in multiple cells together. I don’t think, however, that there’s a way for a cell to have both a name (apart from A6) and data contained therein.
I’ve been using Excel for 13 years, and know nothing of naming cells at all. They’re called A6, BC692 etc. and that’s that.
You could use a macro such as this:
Sub NameThem()
Dim X, Y As Integer
For X = 2 To 5
For Y = 2 To 4
Worksheets("Sheet1").Cells(Y, X).Name = Worksheets("Sheet1").Cells(Y, 1) & Worksheets("Sheet1").Cells(1, X)
Next
Next
End Sub
You’d need to change the “For” statements to reflect the numbers if columns (X) and rows(Y). This one is set for your example. I could write up a self-adjusting version for you if you’d be interested.
Captain Carrot & jjimm: Cells and ranges can have names above and beyond their addresses like A5 or whatever. This feature dates from Lotus 1-2-3 v2.0 & Excel v 1.0. In Excel 2003 look at the Insert >> Name menu to learn more.
This feature makes formulas much easier to understand & debug when the formula in the cell named “GrossProfit” says “=Income - OperatingExpenses” vs having the formula in call A42 saying “=B23-C17”
lee: There are commands to name both columns & rows based on labels at the head of the column or left of a row. But I don’t beleive there is a built-in function to name cells based on the intersection of the two headers.
If you really want that automated, a technique like the PC apeman suggests is the only way I’m aware of.
That worked. I adjusted the sheet name and the column and row numbers and made sure the resulting names were valid and it worked.
That should make it easier to do the rest of the sheet.
Well bloody hell, I stand corrected, and apologise to the OP for my ignorance.
We both learned something today.
The names make it far easier to copy the formulas across and down kind of like using $a$1. I had one portion of the spread sheet done with the cell references and used combination of $s to make potions of the cell references absolute so I could copy then correctly based on one formula and not type out each one, and had excel replace the cell references with names ignoring the $s completely. It was much easier than editing every cell to add $s to the cell references once the formula was correct. What I like best though is that it is easier to read what a formula is doing, although copying the chart to each meal for each day with one cut and paste was pretty nice too.
Excel has a menu item that automatically names cells in the manner you want, it’s Insert:Name:Create … select both the names and the cells, and use the menu item.
w.
That does not seem to work as I needed it to, basing the name on the column and row header.
Wow you guys are pretty feakin good at Excel!
How do you go about making the entire sheet gray except the small part where your table is? Then the active table will have a white background and be surrounded by a blue line. Does this make sense?
Someone emailed me a chart that was like this. But I could not figure out how to do it myself, so I just deleted his chart and it turned everything gray. So then I saved this all gray/blank sheet. And now I just past sheets into it and SAVE AS when I want to make my own. If it doesn’t make sense, I will email someone what I’m talking about. But anyway, how do I just make my own instead of pasting into the blank one?
You hide all of the columns and rows you aren’t using. There may be other ways, but I do this by highlighting the first blank column and hitting ‘shift-ctrl-right arrow’ to highlight the remainder of the blank columns, right-clicking and selecting “Hide”. Do the same for the unwanted rows, and you’re done. If you want to get those rows and columns back, use the menu item “Window -> Unhide”.
It does work as you want it to, but you have to know how to call the name. The method I described names the rows and the columns. If you have a row called “milk” and a column called “carb”, the formula
=milk carb
or
=carb milk
will return the intersection of the row and the column.
w.
Awesome! Thanks!
Are you referring to Page Break Preview? Look under View > Page Break Preview and you will basically be shown what you are going to print. You can drag and drop the blue lines around if you want to change the setup.
intention, that is a very interesting tip. Thanks!
Just a note to say that I’ve created an “Ask the EXCEL guy” thread here for answering any kind of Excel questions.
All the best,
w.
[quote=“the_PC_apeman, post:4, topic:382604”]
You could use a macro such as this:
Sub NameThem()
Dim X, Y As Integer
For X = 2 To 5
For Y = 2 To 4
Worksheets("Sheet1").Cells(Y, X).Name = Worksheets("Sheet1").Cells(Y, 1) & Worksheets("Sheet1").Cells(1, X)
Next
Next
End Sub
You’d need to change the “For” statements to reflect the numbers if columns (X) and rows(Y). This one is set for your example. I could write up a self-adjusting version for you if you’d be interested
How about changing the name of the cell as i change the cell content of the target cell…your macro did not change the name as i change the content of the target cell…pls help. Ty
The other nice thing about using cell names is that you can make them universal, which mean references on other tabs can skip the ‘TAB NAME!’ stuff.