Replicate Dynamic Named Range

Hi,

I have a range of cells that is 10 columns by 5 rows on an 'Entry' Worksheet where I request input from users of 'Task' information (like a Project Tasks).

My need is for the users to be able to copy any of the rows within that range and then paste additional rows, so that they can add any number of additional Tasks.

I have named the range of 10 columns by 5 rows as 'Tasks' and when I copy and paste in additional rows within that same range, I see that the range in the 'Name Manager' extends to included the additional rows. This is exactly what I need.

However, I now need to write the rows, including any additionally added rows, to a separate 'Dataset' Worksheet for backend processing.

Does anyone know how to replicate a dynamically expanding Named Range to a separate Worksheet?

To be clear, I've attempted to get this working, but so far without success.

I've created a separate Named Range called 'Task_Data' in the 'Dataset' Worksheet of 10 columns by 5 rows. Then I used the technique of selecting that range, entering the formula '=Tasks' and using the [Shift] + [Ctrl] + [Enter] key combination to create an array reference back to the 'Tasks' Name Range in the 'Entry' Worksheet.

This works with the initial Named Range, but as soon as I insert extra rows into the 'Tasks' Named Range, they seem to push the bottom row(s) of Tasks outside of the 'Task_Data' Named Range in the 'Dataset' Worksheet.

Any suggestions on how to get the 'Dataset' Worksheet Named Range of 'Task_Data' to automatically expand to include additional rows that are added to the 'Tasks' Named Range would be most appreciated.

Cheers,

Wayne

Use a formula like this:

=IFERROR(INDEX(Tasks,ROWS($1:1),COLUMNS($A:A)),"")

in your first cell, then copy across and down as required.

Hope this helps.

Pete

Hi Pete,

Thanks for the prompt response to my question.

I tried the method you suggested and for the 10 columsn and 5 rows, your formula works perfectly. It brings across each of the values from my 'Tasks' Named Range to the 'Task_data' Named Range (in the same way my array process does).

However, also in the same manner, when I insert an additional row into my 'Tasks' Named Range, while the 'Tasks' Named Range expands, the update to the 'Task_data' Named Range just pushes the rows down and the last row drops out of the defined 10 columns and 5 rows.

What I need is for the 'Task_Data' Named Range to also expand, just like the 'Tasks' Named Range expands.

Do you have any siggestions on how to make the 'Task_data' Named Range that is referencing the 'Tasks' Named Range automatically expand?

Cheers,

Wayne

Hi,

As per my previous reply, the formula provided allows me to replicate what I achieved via the use of an array.

I thought I'd attach the Excel Workbook that includes my 'Entry' Worksheet with my 'Tasks' Named Range along with the 'Dataset' Worksheet that contains the two versions of the linked Named Range (now 'Task_Data1' and 'Task_Data2'), to illustrate that this process is working.

However, what I still can't get to work is the dynamic expansion of either the 'Task_Data1' or the 'Task_Data2' Named Ranges, when I copy and paste a row in the 'Tasks' Worksheet within the green dotted lines (my Named Ranged called 'Tasks'). When I look at the 'Name Manager' information after copying and inserting a row within the green dotted lines, I see that the 'Tasks' Named Range has been extended. I now need a way of automatically expanding the 'Task_Data1' or 'Task_Data2' Named Ranges in the 'Dataset' Worksheet.

I'm hoping the attached Excel Workbook will help clarify my situation and need.

Cheers,

Wayne