Dynamic named ranges

[Dorward]

I have looked around and there are plenty of threads about this topic, but nothing really for what I'm looking for.

Essentially, I have a list of tasks. Each week, I am pasting a new set of tasks into the same sheet. However, many of the values are the same (task headers). These headers are not specified in any real way other than being bolded.
What I need though is from Task X to Task Y-1 to be named Range X. Task Y to Task Z-1 is named range Y. I can use static cell references because the cell reference for those tasks changes.

Any idea how to help me out with this?
Cheers.

 

[Joe4]

Welcome to the Board!

Are you sorting your tasks first so that they are all in order (i.e. all the X Tasks are together, all the Y tasks are together, etc)?

Are there a finite number of different task names, or might that also change each time (not new tasks, but new task names)?

Can you post a small sample of what your data looks like?

 

[Dorward]

All of the tasks part of X will be below. This will always be the case, just the number of tasks below X may differ.

There is a finite amount of tasks names, 10 in total.
I'll post a sample shortly.
Thanks!

 

[Joe4]

OK. There are tools described here that you can use to post screen prints: http://www.mrexcel.com/forum/showthread.php?t=508133

 

[Dorward]

So in the sections below, I want everything from PROJECT MANAGEMENT to before GATING to be "PMO" range, GATING - before BPCM, "GATING" range, BPCM onwards, "BPCM" range. Does that make sense?

187 PROJECT MANAGEMENT
188 Project Management
189 blablablablablablablablablaject
190 Mblablablablablablablablabla
191 Mblablablablablablablablabla
192 Mblablablablablablablablablay
193 Sblablablablablablablablablak
194 Vblablablablablablablablablact Plan
195 Coblablablavatus
337 PM Office Logs/Reports -blablablablablabla
338 CreablablablablablablaReports - Marcblablabla12
339 Conduct blablabla
340 Sblablablablablablablablabla
341 blablablablablablablablablablablabla
342 blablablablablablablablablablablabla
343 blablablablablablablablabla
357 blablablablablablablablablablablabla
415 Incorporate blablablae
423 GATING
430 GATE 4 - Implementation
431 blablablablablablablablabla
432 blablablablablablablablabla
445 BPCM
473 blablablablablablablablablas
478 blablablablablablablablablaa
479 blablablablablablablablablablablabla

 

[Joe4]

I was under the impression from your original post that the task name would be on every row of data, but that does not appear to be the case.
Is the task name always in column A?
You will need a way to differentiate the task names from the other data, which means you will need a pre-defined lists of all tasks name somewhere.

 

[Dorward]

The task name is always in Column A.

Each row is a different task, but BPCM, PMO, etc, are all task types and these are just amongst all tasks.

The Task Types (BPCM, PMO, etc) will remain the same and I have those stored in a list in a hidden column elsewhere.

Here's an idea in pseudo-code:
------------
if cell = "BPCM"
{
go to next row, check to see if = "PMO"
if no, go down to next row. if yes, cell = PMO
}

BPCM:PMO-1 = named range BPCM

-----------
Does that make any sense?

 

[Joe4]

Will the always be in the same order as they are in your "list"?
i.e., BPCM will always be the first task listed, then PMO will be the next, etc.

 

[Dorward]

that is correct. Just the number of tasks under BPCM before PMO will differ.
But the order will also be the same.

 

[Joe4]

I'll look at it tonight. I have an idea, but just one last question.
Each task name should only appear once in column A, right?
i.e. PMO will not appear multiple times