Dynamic Named Range

[andrewc]

Hi,

I have a worksheet of data that is updated every day by pasting new data over the old. Some of the columns are named ranges.

However, the columns may not be in the same place from day to day. For example, the column titled "Tennis" (which is a named range of the same name) may be in column F today and column T tomorrow.

Is it possible to make the named range dynamic to account for this?

Thanks!

[:) Sixthsense :)]

May be use constant column header as identifier

[Olly]

Assuming your column headings are in Row 1, then you can use this formula to create a dynamic named range, of variable row count, in the appropriate column;

Please Login or Register  to view this content.

It works by matching "Tennis" in the header row to identify how many columns to offset the named range, and by counting the nonblank cells in that column to calculate the height (# rows) of the range.

If this doesn't quite suit the range you want, attach a sample workbook showing how your data is laid out, and it's easy to tweak to suit