hi
i have a sheet populated from a dbf(foxPro) file
and my first row contains field names
on the bais of that i want to refer entire column by relative field name
how to achieve this
thanks.
--
hemu

you cant ,, all you can do is use row 1 as a header row

"Hemant_india" wrote:

hi
i have a sheet populated from a dbf(foxPro) file
and my first row contains field names
on the bais of that i want to refer entire column by relative field name
how to achieve this
thanks.
--
hemu

hi Hemu,

I'm not sure if this is exactly what you want but it may give you som
ideas for trying...

nb: this macro is based on the assumption that:
* all the cells in the header row are unique
* there are no blank rows wihin the data (if there are, the named rang
will be inaccurate)
* and the data starts in column A & is continuous across a # o
columns.
Also, be aware that I have not added any error checking.

Are you familar with maros?
If so, put the following in an appropriate module:

Sub CreatingNamesBasedOnHeaders()
Dim i As Long
Dim NumOfCols As Long
Dim RelativeFieldName As String
Dim CurrentSheet As String
NumOfCols = Cells(1, Columns.Count).End(xlToLeft).Column
CurrentSheet = ActiveSheet.Name
For i = 1 To NumOfCols
RelativeFieldName = Cells(1, i)
ActiveWorkbook.Names.Add Name:=RelativeFieldName, RefersToR1C1:= _
"=OFFSET('" & CurrentSheet & "'!R2C" & i & _
",0,0,COUNTA('" & CurrentSheet & "'!R2C" & i & ":R65536C" & i
"))"
Next i
MsgBox NumOfCols & " names have been set up in the current sheet.", _
, "NAMES CREATED BASED ON HEADERS"
End Sub


If not, have a read o
http://www.mvps.org/dmcritchie/excel/getstarted.htm and then open th
Excel file that you want to add the names to, press [alt + F11] (t
open the VB Editor), [alt + i + m] (to add a module), and paste th
above macro into the resulting window. Press [alt + F4] to close the V
Editor.

Now, whenever you want to run this macro on a sheet in this workbook
press [alt + F8] & choose "CreatingNamesBasedOnHeaders" from the lis
of macros. If it the macro will be used in a range of workbooks I'
store it in your personal.xls file (refer to Dave McRitchie's page fo
details).

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

--
broro18
-----------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...fo&userid=3006
View this thread: http://www.excelforum.com/showthread.php?threadid=54415