cell value as cell range

[blairwilson]

hi there
what i require is that the value in a cell to automatically be used as the cell name or cell range
eg if the value incell A1 = my_info then the cell range and value = my_info

the reason for this is that i use a spreadsheet converter for a calculating form on my website but the info that i receive via email is in no order what so ever and isnt in a spreadsheet, however i can copy and paste the info into a spreadsheet, i have managed to create by process of elimination a way of putting the info into some kind of order but it is 1500 rows by 35 colums and takes ages to open.
if i can name the cell ranges using the text that is pasted in as a value automatically i would only need about 150 rows and 15 colums
is there a macro code or any other way of getting this to work
many thanks
blair
<!-- / message --><!-- edit note -->

 

[Tom Urtis]

Are you seriously saying you would want 2250 named ranges in a single workbook? That would be insane, even if down from the original consideration of 52,500. And, if a cell value gets overridden by an updated value in that same cell, your count of named ranges would balloon to an even more grotesque level.

There must be a better alternative to what you are trying to accomplish than that. Maybe by converting al that email data to a csv or xml file and then standardizing it with a macro. Just thinking out loud because no one reading this except you has seen the data, but some different method should be looked at before resorting to all those named ranges.

 

[blairwilson]

hi there
no the info i get in the email when pasted into a spreadsheet is 3 colums by 800 rows
i only require the cells in colum A to use the values to name the cell ranges in colum A
and i only use 1 woork book per email

i paste the emailed info in to cell A1 in sheet 1
and at the moment i have a whole colums of =IF(Sheet1!C126="on",Sheet1!C125,"") formulas and auto filters to weed out the required and non required info
but if i could use the ranges in my formulas then i could replace colums with a single cell
as the info in the email is in a different order depending on there choices cell ranges would be best in my formulas ?

 

[ncalenti]

This should do it for you:

Sub Named_Ranges()

For Each Cell In ActiveSheet.UsedRange.Cells
If Len(Cell.Value) > 0 Then
ActiveWorkbook.Names.Add Name:=Cell.Value, RefersTo:=Cell.Address
End If
Next

End Sub

 

[blairwilson]

hi there
hmmmm i am new to macros and codes etc how? where do i put this ?do i use alt and f11 then ?

sorry and thanks
blair

 

[ncalenti]

Probably the most straight forward way if to go to view-macros-record macro. name it something, then hit stop in the bottom left hand corner of the excel screen. Then View-Macros-Viewmacros. Choose what you just made, hit edit, delete everything, paste in what i gave you. Then save and go to view-macros-view macros, choose named_ranges and hit run. You can also assign it a hot-key.