mass definitions of cell names

	Ok, I have a problem. I have to name a large number of cells in 
different sheets.  I'm hoping that there is a way in a macro, or some 
other way to define these in an easier way other than one by one.  Here 
is an example:

		1		2		3		4
1001        ---------       ---------       ---------       ---------
1002        ---------       ---------       ---------       ---------
1209        ---------       ---------       ---------       ---------
1210        ---------       ---------       ---------       ---------


	This would be the column and row headers for the cells.  Below is how 
the cells would have to be named:


		1		2		3		4
1001        S_1001_01       S_1001_02       S_1001_03       S_1001_04
1002        S_1002_01       S_1002_02       S_1002_03       S_1002_04
1209        S_1209_01       S_1209_02       S_1209_03       S_1209_04
1210        S_1210_01       S_1210_02       S_1210_03       S_1210_04


	There are thousands of cells I have to name in a similar fashion.  Is 
there a shortcut way to define these cells through a macro?  Thanks in 
advance for any help any of you may pass my way.

		-Dan Canham



0
dcanham (24)
6/3/2006 5:55:28 PM
excel 39879 articles. 2 followers. Follow

9 Replies
457 Views

Similar Articles

[PageSpeed] 14

Not a good idea.


-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Grymjack" <dcanham@rochester.rr.com> wrote in message 
news:k2kgg.8939$W97.3054@twister.nyroc.rr.com...
> Ok, I have a problem. I have to name a large number of cells in different 
> sheets.  I'm hoping that there is a way in a macro, or some other way to 
> define these in an easier way other than one by one.  Here is an example:
>
> 1 2 3 4
> 1001        ---------       ---------       ---------       ---------
> 1002        ---------       ---------       ---------       ---------
> 1209        ---------       ---------       ---------       ---------
> 1210        ---------       ---------       ---------       ---------
>
>
> This would be the column and row headers for the cells.  Below is how the 
> cells would have to be named:
>
>
> 1 2 3 4
> 1001        S_1001_01       S_1001_02       S_1001_03       S_1001_04
> 1002        S_1002_01       S_1002_02       S_1002_03       S_1002_04
> 1209        S_1209_01       S_1209_02       S_1209_03       S_1209_04
> 1210        S_1210_01       S_1210_02       S_1210_03       S_1210_04
>
>
> There are thousands of cells I have to name in a similar fashion.  Is 
> there a shortcut way to define these cells through a macro?  Thanks in 
> advance for any help any of you may pass my way.
>
> -Dan Canham
>
>
> 


0
dguillett1 (2487)
6/3/2006 6:12:15 PM
I can almost guarantee that there is a better alternative than creating 
thousands of range names.

Post the functionality you'd like to have and see what solutions are offered.

***********
Regards,
Ron

XL2002, WinXP


"Grymjack" wrote:

> 	Ok, I have a problem. I have to name a large number of cells in 
> different sheets.  I'm hoping that there is a way in a macro, or some 
> other way to define these in an easier way other than one by one.  Here 
> is an example:
> 
> 		1		2		3		4
> 1001        ---------       ---------       ---------       ---------
> 1002        ---------       ---------       ---------       ---------
> 1209        ---------       ---------       ---------       ---------
> 1210        ---------       ---------       ---------       ---------
> 
> 
> 	This would be the column and row headers for the cells.  Below is how 
> the cells would have to be named:
> 
> 
> 		1		2		3		4
> 1001        S_1001_01       S_1001_02       S_1001_03       S_1001_04
> 1002        S_1002_01       S_1002_02       S_1002_03       S_1002_04
> 1209        S_1209_01       S_1209_02       S_1209_03       S_1209_04
> 1210        S_1210_01       S_1210_02       S_1210_03       S_1210_04
> 
> 
> 	There are thousands of cells I have to name in a similar fashion.  Is 
> there a shortcut way to define these cells through a macro?  Thanks in 
> advance for any help any of you may pass my way.
> 
> 		-Dan Canham
> 
> 
> 
> 
0
6/3/2006 6:18:02 PM
	Because of the quirks of the business network setup (read IT 
intransigence), I have to reproduce a database for sales and inventory 
within excel.  I have no ability to get at an access or any other 
database.  Believe me....this is a culmination of a 6 month fight with 
the IM department (Information Maintenance...haha).  They will be stand 
alone sheets that will be reference externally based on cell name and 
product code.  So the column headers would be 1-31 for days of the month 
and the rows would be product codes.  I don't want to do it 
positionally, because that would be bad when new product codes were 
added in the middle of the numerical sequence later.



Ron Coderre wrote:
> I can almost guarantee that there is a better alternative than creating 
> thousands of range names.
> 
> Post the functionality you'd like to have and see what solutions are offered.
> 
> ***********
> Regards,
> Ron
> 
> XL2002, WinXP
> 
> 
> "Grymjack" wrote:
> 
>> 	Ok, I have a problem. I have to name a large number of cells in 
>> different sheets.  I'm hoping that there is a way in a macro, or some 
>> other way to define these in an easier way other than one by one.  Here 
>> is an example:
>>
>> 		1		2		3		4
>> 1001        ---------       ---------       ---------       ---------
>> 1002        ---------       ---------       ---------       ---------
>> 1209        ---------       ---------       ---------       ---------
>> 1210        ---------       ---------       ---------       ---------
>>
>>
>> 	This would be the column and row headers for the cells.  Below is how 
>> the cells would have to be named:
>>
>>
>> 		1		2		3		4
>> 1001        S_1001_01       S_1001_02       S_1001_03       S_1001_04
>> 1002        S_1002_01       S_1002_02       S_1002_03       S_1002_04
>> 1209        S_1209_01       S_1209_02       S_1209_03       S_1209_04
>> 1210        S_1210_01       S_1210_02       S_1210_03       S_1210_04
>>
>>
>> 	There are thousands of cells I have to name in a similar fashion.  Is 
>> there a shortcut way to define these cells through a macro?  Thanks in 
>> advance for any help any of you may pass my way.
>>
>>
>>
>>
>>
0
dcanham (24)
6/4/2006 1:33:38 AM
Grymjack

With all due respect to everyone involved in that project, attempting to 
harvest the data in the way you describe should be abandoned immediately and 
a alternative process should be pursued. I can't imagine an IT professional 
attempting to pull thousands of data cells from a workbook by churning 
through that many range names. But...if that's what you MUST deal with...my 
condolences.

***********
Respectfully,
Ron

XL2002, WinXP


"Grymjack" wrote:

> 	Because of the quirks of the business network setup (read IT 
> intransigence), I have to reproduce a database for sales and inventory 
> within excel.  I have no ability to get at an access or any other 
> database.  Believe me....this is a culmination of a 6 month fight with 
> the IM department (Information Maintenance...haha).  They will be stand 
> alone sheets that will be reference externally based on cell name and 
> product code.  So the column headers would be 1-31 for days of the month 
> and the rows would be product codes.  I don't want to do it 
> positionally, because that would be bad when new product codes were 
> added in the middle of the numerical sequence later.
> 
> 
> 
> Ron Coderre wrote:
> > I can almost guarantee that there is a better alternative than creating 
> > thousands of range names.
> > 
> > Post the functionality you'd like to have and see what solutions are offered.
> > 
> > ***********
> > Regards,
> > Ron
> > 
> > XL2002, WinXP
> > 
> > 
> > "Grymjack" wrote:
> > 
> >> 	Ok, I have a problem. I have to name a large number of cells in 
> >> different sheets.  I'm hoping that there is a way in a macro, or some 
> >> other way to define these in an easier way other than one by one.  Here 
> >> is an example:
> >>
> >> 		1		2		3		4
> >> 1001        ---------       ---------       ---------       ---------
> >> 1002        ---------       ---------       ---------       ---------
> >> 1209        ---------       ---------       ---------       ---------
> >> 1210        ---------       ---------       ---------       ---------
> >>
> >>
> >> 	This would be the column and row headers for the cells.  Below is how 
> >> the cells would have to be named:
> >>
> >>
> >> 		1		2		3		4
> >> 1001        S_1001_01       S_1001_02       S_1001_03       S_1001_04
> >> 1002        S_1002_01       S_1002_02       S_1002_03       S_1002_04
> >> 1209        S_1209_01       S_1209_02       S_1209_03       S_1209_04
> >> 1210        S_1210_01       S_1210_02       S_1210_03       S_1210_04
> >>
> >>
> >> 	There are thousands of cells I have to name in a similar fashion.  Is 
> >> there a shortcut way to define these cells through a macro?  Thanks in 
> >> advance for any help any of you may pass my way.
> >>
> >>
> >>
> >>
> >>
> 
0
6/4/2006 3:06:02 AM
I recently had a client pay me to remove MANY names and restore the formulas 
to cell reference instead of name reference. Keep my name WHEN you need this 
service. Change the procedure now.

OR, at least name only a few and use OFFSET formulas for the others.

However, what you say is doable.

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Grymjack" <dcanham@rochester.rr.com> wrote in message 
news:SLqgg.10024$W97.3736@twister.nyroc.rr.com...
> Because of the quirks of the business network setup (read IT 
> intransigence), I have to reproduce a database for sales and inventory 
> within excel.  I have no ability to get at an access or any other 
> database.  Believe me....this is a culmination of a 6 month fight with the 
> IM department (Information Maintenance...haha).  They will be stand alone 
> sheets that will be reference externally based on cell name and product 
> code.  So the column headers would be 1-31 for days of the month and the 
> rows would be product codes.  I don't want to do it positionally, because 
> that would be bad when new product codes were added in the middle of the 
> numerical sequence later.
>
>
>
> Ron Coderre wrote:
>> I can almost guarantee that there is a better alternative than creating 
>> thousands of range names.
>>
>> Post the functionality you'd like to have and see what solutions are 
>> offered.
>>
>> ***********
>> Regards,
>> Ron
>>
>> XL2002, WinXP
>>
>>
>> "Grymjack" wrote:
>>
>>> Ok, I have a problem. I have to name a large number of cells in 
>>> different sheets.  I'm hoping that there is a way in a macro, or some 
>>> other way to define these in an easier way other than one by one.  Here 
>>> is an example:
>>>
>>> 1 2 3 4
>>> 1001        ---------       ---------       ---------       ---------
>>> 1002        ---------       ---------       ---------       ---------
>>> 1209        ---------       ---------       ---------       ---------
>>> 1210        ---------       ---------       ---------       ---------
>>>
>>>
>>> This would be the column and row headers for the cells.  Below is how 
>>> the cells would have to be named:
>>>
>>>
>>> 1 2 3 4
>>> 1001        S_1001_01       S_1001_02       S_1001_03       S_1001_04
>>> 1002        S_1002_01       S_1002_02       S_1002_03       S_1002_04
>>> 1209        S_1209_01       S_1209_02       S_1209_03       S_1209_04
>>> 1210        S_1210_01       S_1210_02       S_1210_03       S_1210_04
>>>
>>>
>>> There are thousands of cells I have to name in a similar fashion.  Is 
>>> there a shortcut way to define these cells through a macro?  Thanks in 
>>> advance for any help any of you may pass my way.
>>>
>>>
>>>
>>>
>>> 


0
dguillett1 (2487)
6/4/2006 11:43:11 AM
If you insist on this, here is one way

Sub namecells()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a2:a" & lr)
 For i = 1 To 4
  c.Offset(, i).Name = "S_" & c & "_0" & i
 Next i
Next c
End Sub
-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Grymjack" <dcanham@rochester.rr.com> wrote in message 
news:k2kgg.8939$W97.3054@twister.nyroc.rr.com...
> Ok, I have a problem. I have to name a large number of cells in different 
> sheets.  I'm hoping that there is a way in a macro, or some other way to 
> define these in an easier way other than one by one.  Here is an example:
>
> 1 2 3 4
> 1001        ---------       ---------       ---------       ---------
> 1002        ---------       ---------       ---------       ---------
> 1209        ---------       ---------       ---------       ---------
> 1210        ---------       ---------       ---------       ---------
>
>
> This would be the column and row headers for the cells.  Below is how the 
> cells would have to be named:
>
>
> 1 2 3 4
> 1001        S_1001_01       S_1001_02       S_1001_03       S_1001_04
> 1002        S_1002_01       S_1002_02       S_1002_03       S_1002_04
> 1209        S_1209_01       S_1209_02       S_1209_03       S_1209_04
> 1210        S_1210_01       S_1210_02       S_1210_03       S_1210_04
>
>
> There are thousands of cells I have to name in a similar fashion.  Is 
> there a shortcut way to define these cells through a macro?  Thanks in 
> advance for any help any of you may pass my way.
>
> -Dan Canham
>
>
> 


0
dguillett1 (2487)
6/4/2006 11:55:08 AM
Don Guillett wrote:
> If you insist on this, here is one way
> 
> Sub namecells()
> lr = Cells(Rows.Count, "a").End(xlUp).Row
> For Each c In Range("a2:a" & lr)
>  For i = 1 To 4
>   c.Offset(, i).Name = "S_" & c & "_0" & i
>  Next i
> Next c
> End Sub

Thanks for the help
0
dcanham (24)
6/5/2006 5:59:48 PM
You really shouldn't do this but if you do keep my email to pay me to change 
it back.

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Grymjack" <dcanham@rochester.rr.com> wrote in message 
news:oi_gg.10027$3B.7489@twister.nyroc.rr.com...
> Don Guillett wrote:
>> If you insist on this, here is one way
>>
>> Sub namecells()
>> lr = Cells(Rows.Count, "a").End(xlUp).Row
>> For Each c In Range("a2:a" & lr)
>>  For i = 1 To 4
>>   c.Offset(, i).Name = "S_" & c & "_0" & i
>>  Next i
>> Next c
>> End Sub
>
> Thanks for the help 


0
dguillett1 (2487)
6/5/2006 9:15:17 PM
lol.....I will do that


Don Guillett wrote:
> You really shouldn't do this but if you do keep my email to pay me to change 
> it back.
> 
0
dcanham (24)
6/7/2006 4:12:42 PM
Reply:

Similar Artilces:

How to I set the margins for a cell in Excel?
Though I have word wrap on, the words are running over into the next cell. This could have something to do with the fact that I have hard-returns in the cell. I wanted to ensure the margins weren't set to -.5 (or something like that) which would allow this kind of short run-over. Are you sure you've toggled wraptext? Format|cells|alignment tab I've never seen text bleed over to adjacent cells with this toggled correctly. blinko wrote: > > Though I have word wrap on, the words are running over into the next cell. > This could have something to do with the fact tha...

Selected cells grow and data entry impossible EXT is dissabled
How do I dissable this option? I select a cell and move the cursor which selects other cells. I also cannot enter data anywhere or select any options in the tool bar after slecting a cell. Extended Selection Option is dissabled and verified through the status bar. Sometimes the mouse buttons stick. Gently bang all the buttons (including the wheel button if you have it) to see if that helps. David McRitchie has some notes about this kind thing at: http://www.mvps.org/dmcritchie/excel/ghosting.txt Allanhart42 wrote: > > How do I dissable this option? I select a cell and move the cu...

Cell changes color
I have a range that is all colored yellow. For some reason, one of the cells changes to white when the user enters a number in it. Anyone know why and how to prevent? Thanks. What you are describing can occur if conditional formatting has been applied to the cell. -- Gary's Student "JG Scott" wrote: > I have a range that is all colored yellow. For some reason, one of the > cells changes to white when the user enters a number in it. Anyone > know why and how to prevent? > > Thanks. > > "JG Scott" <jgscott3@bellsouth.net> wrote i...

Shading Blank Cells Between Values?
Hello, I have a program that I would like to keep track of pending leave for our employees. There is a sheet for every employee. I enter leave start dates and duration into these sheets. There is a master sheet with all employees names that looks like a calendar. A function places "Start" in the cell of the start date of leave, and "End" in the cell of the end date of leave. I can use conditional formatting to shade the cells that have "Start" and "End" in them, but I can't figure out how to shade the blank cells between the two. I...

cell format changing when when using search and replace.
I have a macro that places a large number of pictures on a spreadsheet. I would like the pictures to show as "placeholders" to speed up viewing/editing. The only problem is that this feature (under Tools/Options/View) does not work. All the images are being display normally. I have tried this in Excel 2000/2003/XP with the same result. This feature works great in MS Word. Please help. To see what I'm talking about. Go to Tools/Options/View and check the "Show Placeholders" option. Now insert a picture or clipart. The image will be displayed normally instead of just a b...

Transfer Table Name?
RMS V 1.3.1006 What is the name for the Transfer (in/out) table within RMS? I need to get after some xfers inbound to a closed location whose database we do not have to flip to. I would like to get at these via HQ Admin and delete them for the location specific. Thanks everyone! -- Jocelyn Jocelyn, These are stored in the PurchaseOrder/PurchaseOrderEntry tables. - Evan Culver New West Technologies "jocelynp" <jocelynp@discussions.microsoft.com> wrote in message news:E904E333-83B4-4174-A2F4-F7D27530597D@microsoft.com... > RMS V 1.3.1006 > > What is the name ...

Excel Cell Formatting, multiple lines
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to force a line wrap within a cell in Excel, similar to the way Shift^Return forces a line wrap in a table cell in Word? Control+Command+Return as well as Control+Option+Return. Have a look in Excel Help for others: Keyboard shortcuts used in Excel. HTH |:>) Bob Jones [MVP] Office:Mac On 5/22/10 1:51 PM, in article 59bb8db7.-1@webcrossing.JaKIaxP2ac0, "IHS@officeformac.com" <IHS@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (S...

How can I get the active cell in a worksheet to be highlighted in.
When working on a very large spreadsheet, I find it difficult to identify the active cell quickly and it would be helpful it it could be highlighted in a different color than all the rest. Is there any way to do this? Hi not quite what you have asked for but see: http://www.cpearson.com/excel/RowLiner.htm Note: This kind of event procedure will disbable the UNDO functionality -- Regards Frank Kabel Frankfurt, Germany "DizzyD" <DizzyD@discussions.microsoft.com> schrieb im Newsbeitrag news:DA26FEF3-1092-429E-BDB8-8F5319F31B2B@microsoft.com... > When working on a very la...

Cell subtraction
In E17 I placed =If(isblank(A17),"===",B17-C17) In A17 I have the date 4/4/05 In B17 is the negative number -30.33 In C17 is the number 0.00 In E17 I get 30.33 instead of -30.33 How can I fix this to get -30.33 in E17 Also if A17 is blank/empty and B17 is empty and C17 is 500 then I get #VALUE! in E17 instead of === gudway@charter.net Your formula works correctly when values are keyed into A17 and B17 and C17. What formulas do you have in those cells? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence wit...

need a cell to highlight
can this be done? when i put somthing in "D3" say a letter "Y" i would like for "F3" to highlight say a color say " blue" can that be done?? jo -- Message posted from http://www.ExcelForum.com Format>conditional formatting, formula is =EXACT($D$3,UPPER($D$3) click format and select pattern, click OK twice -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "jladika >" <<jladika.17jkuj@excelforum-nospam.com> wrote in message news:jladika.17jkuj@excelforum-nospam.com... >...

Count consecutive cells if negative
Hi all, Is there a way to do this in excel? I have a column of numbers and I would like to count what the max number of consecutive negatives are. For instance, if I have a series of numbers (1,3,5,-2,-5,3,-7)), I would like it to return 2 as the max number of consecutive negatives. Thanks in advance! --- Message posted from http://www.ExcelForum.com/ There may be a more elegant way, but here is one that worked for me: Assuming the data resides in Column A (I will use A2:A10 for illustration), I copy the following formula down next to the data in Column B from B2 to B10: =IF(A2<0,...

pethow do i insert a Yes/No function into a cell in Excel?
How do i insert a Yes/No function into a cell in Excel? petethomson Wrote: > How do i insert a Yes/No function into a cell in Excel? Hi pete Try an If function eg IF(A1=10, "Yes","No"), this is saying if A1 = 10 return the value Yes, if not return No -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=467552 ...

How do I copy data from one cell to another.
Hello. I'm having trouble creating a macro that will let me cut postcodes from one cell and paste into another cell. I would appreciate if anybody can help me. What I have, is a large excel spreadsheet with a couple of thousand names and addresses. The address fields start in column B through to G. Each line of the address should be in a separate cell within each row, but the postcodes have all ended up in the same cell as the last line of the address. I need to cut the postcodes out of these cells and paste them into column H which is empty. The addresses are different lengths, leavi...

How do you use ComboBox's as worksheet names in SUM formulas
I am trying to write a SUM formula that would use the values of tw combo boxes to SUM a range of worksheets. The values for these boxes are in cells: Analysis!B7 Analysis!D7 These values are also dates, which are also the names of the worksheet in the workbook. Lets say the selected dates in the two combo boxe are 21-Jun-04 and 12-Jul-04, which means the two cells above would hav the following values: Analysis!B7 = 21-Jun-04 Analysis!D7 = 12-Jul-04 These cells are in text format. There is a worksheet named 28-Jun-0 in between the previously named sheets, so I am able to sum all three. I...

Finding cells with leading characters
My spreadsheet looks like this: A 1 000000 2 182068 3 198506 4 200081 5 368010 6 580905 7 680001 8 686850 9 690060 10 700012 11 701068 12 802054 13 920211 14 968902 Using EDIT/FIND, when I enter '68' in the "Find What:" box, Excel finds A2, A5, A7, A8, A11 and A14 because their values contain '68'. What do I enter in the "Find What:" box to find the cells whose values BEGIN with '68'? (In this example, it would find ...

How do I change color of active cell in Excel
I would like the active cell (cell I am currently working in) to be a different color. Example: I am working in cell AD58 I would like for that cell to be blue, then as I move through the sheet, the current active cell would be blue. right click sheet tab>view code>insert this>SAVE to use for only one cell change Set MyRng = Target.EntireRow to Set MyRng = Target ===== 'McCurdy.Here is something inspired by Don Guillett. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyRng As Range Set MyRng = Target.EntireRow Application.EnableEvents = False On Error GoT...

Pivot Table Report Parameters to be named and saved like 'View' na
I have generated different Pitot Table Reports from the Sales Invoice Register. These reports give rgionwise, productwise, timewise info in different combinations and appearance. Unfortunately, as I change the parameter of the report, it replaces the existing report and generates the new report. One solution is to have each report in separate worksheet. But this will substantially increase the number of worksheets. What I would prefer is to give a name to these parameters and save it. Whenever I want a particular report, all I have to do is to select that name from the scroll down list...

Way to mass change supplier for multiple items at once
Hi all, Is there a way to mass change the supplier for multiple items at once. When we have a change in who we order a product line from we have to go into each item individually to update the supplier info. Is there a easier way to update multiple items? Thanks, Skyler On 7/14/2010 12:37 PM, Skyler Lister Aley wrote: > Hi all, > > Is there a way to mass change the supplier for multiple items at once. When > we have a change in who we order a product line from we have to go into each > item individually to update the supplier info. Is there a easier way to &g...

Attaching Macros to cells
Does anyone know If you can attach a VBA macro to a cell so that it runs when you enter out of the cell ...

Name Manager in Excel 2007
Hi, all I have a workbook which I created in Excel 2003. I am now editing it in Excel 2007. In the Name Manager, there are some named ranges I want to delete, but the DELETE button is greyed out. In fact, the ones I want to delete (but can't) have a VALUE of {...}. Is this a bug, or is there another way to delete the ones I want to delete? Thanks for your help! Tucson Jeff ...

Can I conditionally lock/unlock some cells
Hi I am making a journal on a spread sheet whereby each row is a successive date. I would like to make it so that the user can ONLY change the current row (date = TODAY). I figured out how to use conditional formatting to make the current date row a different color but I would like to go further and make it so the user can only change the current line. Can this be done? How? Thanks in advance Hi, you can use 'Data - Validation' and check if the date in one cell is equal to TODAY() Frank Another way is to use a macro that unprotects the worksheet, locks all the cells, unlo...

Formatting Cells Containing Date Fields That Are Text
Someone here at work imported into Excel a column of text data that looks like mm/dd/yyyy form but need to be mmddyyyy. I thought I could simply highlight the columns, click on Format/Cells/Custom and create mmddyyyy. I do this but the data doesn't change UNTIL I highlight each cell and press F2 or double-click in each cell or create a formula using Right, Mid, etc. When I press F2 or double-click, I notice that an X and a check mark both appear to the right of the Row/ Column box. There's got to be an easier way to do this besides 'onesie-twosie' but we can't figure it...

auto fill data into a cell from a lookup table
Does anyone know how to set up a cell formula so that if the data in a cell (such as a county name) is entered, an amount is placed in the cell in the next column? Hi use VLOOKUP to do this say you have your list of countries and amounts on sheet 2 in the range A1:B100 and then on sheet 1 you want to type a country name in cell A1 and have the amount appear in B1 the formula for B1 would be =VLOOKUP(A1,Sheet2!$A$2:$B$100,2,0) which says, look at the value in A1, find it in the first column of the table in Sheet2 and return the associated value from the second column of this table whe...

limit number of characters in a cell
I am creating an application form to be used electronically. I want a particular cell to be free text, so users can write what they like (ie their name), but I want to limit the number of characters they can use. Can this be done? I have put text wrap on the cell, but it still allows me to enter more information than fits into the box. I want it to stop users entering info when the visible cell is full. Rob, Teesside, UK. "abfabrob" wrote: > I am creating an application form to be used electronically. I want a > particular cell to be free text, so users can write wh...

help! named ranges/data validation changing by itself?
I have a worksheet that contains the lists I want to use in various data validation cells on other sheets I created a named range for each list, using Steve Bullen's autoexpanding ranges in case the user wants to add/delete items I took out the fixed referencess ("$") in each named range, because it is possible that the end user will add rows or columns in my list source sheet, and I wanted to make sure the workbook was flexible enough to handle it. The problem is that the workbook seems to be changing the source ranges, without columns being added or deleted in that source sh...