purely dynamic named range

[ajm]

good morning folks, I have had a user insert rows above a table in a model which has thrown out associated macros. i normally go through after the review period and lock things down but wasn't quick enough. also, this is one of my superusers so i expect he will want to add things after i hand over.

so, i am trying to make the model as dynamic as possible.

first question: how to include dynamic references in the dynamic named range formula which uses INDEX

I have so far for "MyRngToCopy":

=RngFirstCellFwdPlan:INDEX('Forward Plan'!$1:$1048576,COUNTA('Forward Plan'!$A:$A)-1,COUNTA(RngFPlanHDR))

I have made the starter cell into a named range (RngFirstCellFwdPlan) and also the number of columns wide (RngFPlanHDR).

How do i amend the "'Forward Plan'!$1:$1048576" so that it starts at the same row as the table using ?

 

[Gimics]

Hey ajm,

Just clarifying because of the format of your message - are you looking for a solution for your formula in the Excel sheet, or are you looking for a solution that would be used in VBA code/macros?

Just a bit tough to tell with the formatting of your "Code" section.

Thanks!

 

[ajm]

Hi Gimics. sorry for keeping you waiting. hope you weren't holding your breath. i was trying to make the range name refers to formula more dynamic. Have resolved.