In order to help with formulas I was going to name cells. This is because the info is in horizontal rows but I am using it to create a journal which will be vertical.

There are various names on each row.

There are lots of rows is there any short cut so that all the cells on row 1 are name1, age1 etc then the second row it would be name2, age2. Apart from me manually changing everything?

On Fri, 13 Apr 2012 02:55:17 -0700 (PDT), Louja wrote:

In order to help with formulas I was going to name cells. This is because the info is in horizontal rows but I am using it to create a journal which will be vertical.

There are various names on each row.

There are lots of rows is there any short cut so that all the cells on row 1 are name1, age1 etc then the second row it would be name2, age2. Apart from me manually changing everything?

I don't think there is without using VBA.

Depending on your formulas, you may be able to just name the range (Have labels in row one; select your table; then use the Create Names wizard).
You could then refer to each item using the Index function: =INDEX(Name,1)

Looks like the VBA course I want to go on should be sooner rather than later.

Once the spreadsheet has been set up I shouldn't be adding more rows. Just need to do it for the set up.

Thanks!

On Fri, 13 Apr 2012 04:19:46 -0700 (PDT), Louja wrote:

Looks like the VBA course I want to go on should be sooner rather than later.

Once the spreadsheet has been set up I shouldn't be adding more rows. Just need to do it for the set up.

Thanks!

I'm not sure if this is a response to me or not. Although you can name each cell using VBA, naming the ranges, and then referring to the individual elements using the INDEX function, as I described earlier, is probably a better way of proceeding.

Each name that you use utilizes some memory, and the total number of names is memory limited. You are more likely to run into problems with your idea. I'm sure there are more efficient ways of doing what you want to do.

As an exercise, not recommended for "real use", one way of using VBA is below. Note that the macro will first delete ALL names in the workbook, so do not have any NAME'd ranges, that you want to keep, before you run the macro. If you run out of memory for the names, there will be an error message.

First enter all your data in rows.
Make the appropriate changes in the macro to the Name list (aNames)
Note that all of the names are followed by an underscore prior to their index number, so as to avoid naming conflicts with cell references (which are not allowed).

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

======================================
Option Explicit
Sub NameRanges()
Dim aNames
Dim r As Range, c As Range
Dim n As Name
aNames = Array("Name_", "Age_", "Date_", "Other_")
Set r = ActiveSheet.UsedRange

For Each n In Names
n.Delete
Next n

For Each c In r
Names.Add Name:=aNames(c.Column - 1) & c.Row, _
RefersTo:="=" & c.Worksheet.Name & "!" & c.Address
Next c
End Sub
======================================

Hi,

Thanks for your help. If there is a way better than naming I am happy to use it.

With my limited excel knowledge I thought that would be the best way to do it.

Thanks for your help. I will have a look.