Actually, I think I just figured it out. Because the formula needs to use
relative cell references starting from K15, this is the cell I selected
before assigning the formula. However, when I removed that select statement,
it seems to work fine now.
"JHARRIS133" wrote:
> This formula needs to get down rows, so I cannot use an absolute reference to
> any of the arguments with the exception to $E$9, which will stay constant.
> That was the reason why I didn't do them all like that.
>
> "Joel" wrote:
>
> > Did you ever ask the question why the $E$9 didn't change rows???
> >
> >
> > Put a dollar sigh infront of the 15 and it wil solve your problem!!!!!!!!!!
> >
> > "JHARRIS133" wrote:
> >
> > > Hello,
> > >
> > > I have a worksheet that has a named formula that takes various arguments
> > > from the current row and performs a function. This is for an app that
> > > consolidates multiple status reports into a master report, so I need to have
> > > different names for the function, one for the master version, and one for the
> > > source version. The master version of this formula will always exist in the
> > > spreadsheet, but I would like to dynamically create the other version, based
> > > on what the master version refersto.
> > >
> > > Here is my formula:
> > > =getStatus('Status Report'!$K15,'Status Report'!$L15,'Status
> > > Report'!$E$9,'Status Report'!$P15,'Status Report'!$R15)
> > >
> > > The master version of this formula is called CGIStatusColor, and the new
> > > version I am trying to create is called CustStatusColor, but it should take
> > > exactly the same arguments. Here is the code I use to create this new named
> > > formula:
> > >
> > > For Each c In rngStatusColors.Cells
> > > wksStatusReport.Range("J15").Select
> > > Dim nm As Name
> > > For Each nm In ThisWorkbook.Names
> > > With nm
> > > If (Left(nm.Name, 3) = "CGI") Then
> > > MsgBox nm.RefersTo
> > > ThisWorkbook.Names.Add Name:="CustStatusColor",
> > > RefersTo:=nm.RefersTo
> > > End If
> > > End With
> > > Next nm
> > > c.Formula = "=CustStatusColor"
> > > Next c
> > >
> > > As you can see from the msgbox line above, I've looked at the RefersTo
> > > formula just prior to assigning it, and it is correct. However, when I check
> > > the RefersTo in the Define Name box, here is what is actually entered:
> > >
> > > =getStatus('Status Report'!$K29,'Status Report'!$L29,'Status
> > > Report'!$E$9,'Status Report'!$P29,'Status Report'!$R29)
> > >
> > > I cannot figure out why the row references are changing to be row29 rather
> > > than row15, as they are immediately before I assign them.
> > >
> > > Please help!
> > > Thanks.
|
Similar Threads
