Excel Cell Names

[Vittles]

I have a spreadsheet where the 3rd row is going to be filled with various people's last names. Is there a way to change the Cell Name to be equal to the value in that cell? For example if cell B3 has "Smith" entered as a value, can "Smith" also be automatically set as the cell name?

Any ideas would be great as this worksheet is going to be cumbersome in that many columns will be used and it would be nice to be able to pick a name from the cell name box to go to that column.

 

[SkipVought]

V,

Do you reall want a single cell named as the VALUE in the cell?

How do you intend to use the Name value instead of the Cell Value?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

Have a look at the Names collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Vittles]

The basic idea is for the staff to be able to select a person's name from the Name Box (based on the values entered into cells in that single row) and go right to that person's column of information. We are limited to using excel or I would have done this in Access with a combobox-criteria search.

I know I can re-type each cell value into the cell name box, but I was hoping for a more automated way to save time and duplicate entry.

Any ideas?

 

[SkipVought]

Then do this...

1) select ALL the data including the heading row and then Insert/Name/Create -- Create Name in TOP row. You can marco record, if necessary.

selecting the name in the name box will correspondingly select the data under the name.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

I think that I'd be more apt to put ALL names in one column and haf the corresponding data to the left (table style)

Then use the AutoFilter to select a name.

The method that you described is certainly NOT a standard best method and is fraught with problems regarding data analysis and reporting.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

oops....

I think that I'd be more apt to put ALL names in one column and have the corresponding data to the RIGHT (table style)



Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Vittles]

The column on the left side has another value list of expertise (see below), else I would have done that.

Expertise Column \ Name1 \ Name2
Expertise1 \ High Low
Expertise2 \ Med High


I have autofilter turned on as well for the header row, so that the staff can pull by 'levels' of expertise and such.

Your idea about the insert/name was a good one. I will do it a couple times over the timeperiod they are entering those names, but it still saves alot of time & patience.

 

[SkipVought]

[tt]
Name Expertise Expertise Value
Name1 1 High
Name1 2 Low
Name2 1 Med
Name2 2 High
[/tt]

Fundamentals Of Relational Database Design

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Vittles]

There will be about 300 names entered (over a period of time, dependent on when that person is contacted-none entered now) and about 100 Expertise (most of which I can add now, but others will be added later).

I really wish I could use Access, but it isn't allowed for this project.

 

[PHV]

about 300 names
No more than 255 columns in Excel ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

No reason that Excel cannot be used as a database as I described -- 3 columns.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

Skip, my note was to inform Vittles that their current design is inappropriate unless (s)he likes to hit the walls.

 

[SkipVought]

It's inappropriate on several counts, it seems.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

Sure, as usual then one confuses spreadsheet and database ...

 

[SkipVought]

Even if you could use Access, it would be GROSSLY inappropriate to cast you current design in an Access table.

Any design that adds data by adding columns is fraught with blood, sweat and tears, for no good reason at all.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Vittles]

I would design it differently if it was in Access, but some of the basic structure isn't in my hands, unfortunately.

If I change it so that there are 3 columns (& get them to approve that layout), Wouldn't the staff have to enter the names and the individual expertise for each name afterwards? There are going to be clerks doing the entry, so they wanted to have a structure set up, so they would only have to enter limited info...

Don't mean to take up so much of your time, but I am curious now.

 

[AnotherHiggins]

Skip and PHV - I think y'all are both arguing the same side.

The way that Vittles currently have the table set up is inappropriate because there "will be about 300 names entered".

Names across the top simply won't work - there is a limit of 256 columns, as PHV pointed out.

Setting it up the other way around, with names going down will work fine. Even if you use a new column for every expertise, it will still work since there will only be "about 100 Expertise".



[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

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

 

[SkipVought]

Your current structure won't work!

Did you read the Fundamentals Of Relational Database Design?

There is no shortcut to a sound database approch.

Under your current approch, you will run into many problems that will take you and others MUCH MORE TIME AND EFFORT than correct design and data entry.

Unfortunately, when non-technical managers make technical decisions, they end up paying MUCH MORE for improper and unpractical designs down the pike.

But who am I to say, having done this kind of work and observed the pitfalls for the past 25 years.

BTW, you have my permission to copy this post and give to whoever is responsible for making the decision to use the your current structure.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}