Use formula to name cells 1

[patme]

Is there a way to use a formula, VBA, etc., to use the info in cells to name another cell?

For example, in the cells below, I would like to NAME Cell C1 "401", and NAME C2 "402", etc.
A B C
1 40 1
2 40 2
3 40 3

 

[AnotherHiggins]

Just to be clear - you are talking about Named Ranges, right? Not just populating column C with a combination of what is in columns A and B?

What would you use that for?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[patme]

Correct. I have a list of wrestlers that we want to rank according to weight (Column A) and seed (Column B), and their names are going in Column C. I want to put their names on a bracket on another worksheet, and want to reference the named cell in the bracket. I've got it to work fine, but I have about 300 kids and don't want to have to manually name each cell. the reason for the named cells is: I may have to insert new names the day of the tournament, etc., and I've noticed that the name stays with the cell, regardless of what data is in it.

Clear as mud?!

 

[AnotherHiggins]

Hmm.

Well, you could do this with VBA using something along the line of

For Each cell In Selection
ActiveWorkbook.Names.Add cell.Offset(, -2).Value & _
    cell.Offset(, -1).Value, cell
Next cell

Except for the fact that you can't name a range with all numbers. 401 is not a valid Range Name. You could get around this by adding a letter to the beginning and end of the Name string, but I don't think this is the best way to proceed.

How are you hoping to list them on the "brackets" sheet? There is probably an easier way.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[patme]

Actually, I'm adding the word "SEED" to the beginning of each name. So I have Seed401, Seed402, etc.

 

[SkipVought]

If you were to concatenate column A values with column B values in column D, like this...
[tt]
=A1&B1
[/tt]
then select C13 per your example and...

Insert/Name/Create - Create names in RIGHT COLUMN

you would have your named ranges WITHOUT CODE.

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[AnotherHiggins]

In that case, this will work:

myRowCount = Application.WorksheetFunction.CountA(Range("C:C"))

For Each cell In Range("C2:C" & myRowCount)
ActiveWorkbook.Names.Add "Seed" & cell.Offset(, -2).Value & _
    cell.Offset(, -1).Value, cell
Next cell

I still think there is a better way using nothing but worksheet formulas, but this will name all of the cells like you want. I just don’t have enough information yet.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Nice, Skip! I didn't know about that.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[patme]

That worked perfectly, thanks! Star for YOU!

 

[SkipVought]

John,

I most often use the Names in Top Row checkbox. Code it in conjunction with CurrentRegion

with application
  .displayalerts=false
   objSheet.[A1].CurrentRegion.CreateNames Top:=True, Left:=False
  .displayalerts=true
end with

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}