On Feb 13, 1:44*pm, Dave Peterson wrote:
ActiveCell.offset(1,0).Formula _
* * = "=" & worksheets("date summary").Cells(intRw, _
* * * * * activesheet.Range("FullNameValue").column).Address (external:=True)

I think.

wrote:

<<snipped





- Show quoted text -

Sorry this is so confusing.

1. *The name of the sheet getting the formula is "888"

2. *If I placed the formula on the sheet it would be "='Date Summary'!
W43
(because info for hull number 888 is on row 43 of sheet Date Summary,
*and column 'W' contains 'FullName' info.)

3 & 4. *I have a column with the header "FullNameValue" *which is a
concatenation
of the ship name, type, and hull number. *I use a macro that defines a
named
range for each column, and uses the column header as the range name.
So, I
also have a rangenamed"FullNameValue". *It's RefersTo value shows as
="$W:$W". *It is a Workbook levelnamedrange, so it is not 'local' to
any one
sheet.

Note: *my code worked great when i had hard column references: i.e. -
* *ActiveCell.offset(1,0).Formula = "="+strDS + "W" + strRw

where strDS="'" + "DATE SUMMARY" + "'" + "!" (sheet reference),
"W" is column W which contains "FullNameValue" data,
and strRw which is a string representing the row the ships info is on.

Thanks again for your time,
hglembin

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Getting There!!

I used your previous recommendation (minus the "activesheet" prior to
"Range" since range is on the Date Summary sheet) and I've been able
to get it to work for one of my named ranges.

Reviewing my ranges via the "Define Name" dialog box, I noticed that
the range for which the formula works is different. The "Refers to"
value for that range is "='DATE SUMMARY'!$S$S".
The refers to value for all other named ranges displays ="$W:
$W" (with correct letters for each column). If I delete and manually
create the named ranges for column W, the 'Refers to' value
changes from ="$W:$W" to ='DATE SUMMARY'!$W$W. I need the
program to reset named ranges each time the user leaves the "Date
Summary" sheet to ensure correct columns
are used. How can I change the macro that creates the named ranges to
ensure the Refers to format matches the one provided by manually
defining ranges?

Optimism has returned! Thank you for sharing your knowledge!

v/r,
hglembin